package com.b2c.repository.oms;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.b2c.entity.result.PagingResponse;
import com.b2c.common.utils.DateUtil;
import com.b2c.entity.ErpGoodsStockInfoEntity;
import com.b2c.entity.ErpOrderEntity;
import com.b2c.entity.ErpOrderItemEntity;
import com.b2c.entity.WaitSendGoodsSpecModel;
import com.b2c.entity.apitao.PddOrderStatis;
import com.b2c.entity.apitao.PddOrderStatisVo;
import com.b2c.entity.apitao.UpdOrderTagReq;
import com.b2c.entity.erp.ErpGoodsSpecEntity;
import com.b2c.entity.erp.enums.ErpOrderSourceEnum;
import com.b2c.entity.erp.enums.StockOutFormStatusEnum;
import com.b2c.entity.pdd.*;
import com.b2c.entity.result.EnumResultVo;
import com.b2c.entity.result.ResultVo;
import com.b2c.entity.enums.EnumErpOrderSendStatus;
import com.b2c.entity.enums.erp.EnumGoodsStockLogSourceType;
import com.b2c.entity.enums.erp.EnumGoodsStockLogType;
import com.b2c.entity.enums.erp.EnumOrderReturnStatus;
import com.b2c.repository.Tables;
import com.b2c.repository.utils.OrderNumberUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 拼多多订单Repository
 */
@Repository
public class OrderPddRepository {
    private static Logger log = LoggerFactory.getLogger(OrderPddRepository.class);
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    DataSourceTransactionManager dataSourceTransactionManager;
    @Autowired
    TransactionDefinition transactionDefinition;

    public OrderPddEntity queryOrderBySn(String orderSn){
        try{
            var pddOrder= jdbcTemplate.queryForObject("SELECT * FROM dc_pdd_orders WHERE order_sn=?", new BeanPropertyRowMapper<>(OrderPddEntity.class), orderSn);
            var pddOrderItems=jdbcTemplate.query("select * from dc_pdd_orders_item where order_id=?",new BeanPropertyRowMapper<>(OrderPddItemEntity.class),pddOrder.getId());
            pddOrder.setItems(pddOrderItems);
            return pddOrder;
        }catch (Exception e){
            return null;
        }
    }

    /**
     * 拦截订单
     * @param orderSn
     * @param refundId
     */
    @Transactional
    public ResultVo<Long> interceptOrder(String orderSn, Long refundId,String msg,Integer afterSalesType) {
        try {
            var pddOrder = jdbcTemplate.queryForObject("SELECT * FROM dc_pdd_orders WHERE order_sn=?", new BeanPropertyRowMapper<>(OrderPddEntity.class), orderSn);


            //更新退货单快递信息
            jdbcTemplate.update("UPDATE dc_pdd_refund SET tracking_number=?,tracking_company=?,sign=?,after_sales_type=?,`describe`=? WHERE id=?",
                    pddOrder.getTracking_number(),
                    pddOrder.getTracking_company(),
                    msg+"："+DateUtil.getCurrentDateTime(),
                    afterSalesType,"拦截："+pddOrder.getTracking_number(),
                    refundId);

            //更新订单refund_status
            jdbcTemplate.update("UPDATE dc_pdd_orders SET auditStatus=2,refund_status=2,after_sales_status=1 WHERE id=?",pddOrder.getId());

            return new ResultVo<>(EnumResultVo.SUCCESS, "成功",refundId);

        } catch (Exception e) {
            throw e;
        }
    }

    /**
     * 插入拼多多订单消息推送（只有订单编码）
     * @param shopId
     * @param tid
     * @return
     */
    @Transactional
    public ResultVo<Long> insertOrderForMessage(Integer shopId, String tid) {
        try {
            log.info("开始插入拼多多订单消息");
            var orderExist = jdbcTemplate.queryForObject("SELECT COUNT(0) FROM dc_pdd_orders WHERE order_sn=? and shopId = ?", Integer.class, tid, shopId);
            if (orderExist == 0) {
                //不存在才添加
                /*****1、添加order*****/
                StringBuilder orderInsertSQL = new StringBuilder();
                orderInsertSQL.append("INSERT INTO dc_pdd_orders");
                orderInsertSQL.append(" SET ");
                orderInsertSQL.append(" order_sn=?,");
                orderInsertSQL.append(" shopId=?,");
                orderInsertSQL.append(" trade_type=0,");
                orderInsertSQL.append(" confirm_status=0,");
                orderInsertSQL.append(" free_sf=0,");
                orderInsertSQL.append(" group_status=0,");
                orderInsertSQL.append(" capital_free_discount=0.0,");
                orderInsertSQL.append(" seller_discount=0.0,");
                orderInsertSQL.append(" platform_discount=0.0,");
                orderInsertSQL.append(" updated_at=?,");
                orderInsertSQL.append(" refund_status=0,");
                orderInsertSQL.append(" is_lucky_flag=0,");
                orderInsertSQL.append(" order_status=0,");
                orderInsertSQL.append(" postage=0.0,");
                orderInsertSQL.append(" discount_amount=0.0,");
                orderInsertSQL.append(" goods_amount=0.0,");
                orderInsertSQL.append(" pay_amount=0.0,");
                orderInsertSQL.append(" after_sales_status=0,");
                orderInsertSQL.append(" order_confirm_time=0, ");
                orderInsertSQL.append(" auditStatus=-9 ");


                KeyHolder keyHolder = new GeneratedKeyHolder();
                jdbcTemplate.update(new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(orderInsertSQL.toString(), Statement.RETURN_GENERATED_KEYS);
                        ps.setString(1, tid);
                        ps.setInt(2, shopId);//来源于dc_shop id
                        ps.setString(3, DateUtil.getCurrentDateTime());
                        return ps;
                    }
                }, keyHolder);

                Long orderId = keyHolder.getKey().longValue();
                log.info("添加订单消息成功：" + orderId);
                return new ResultVo<>(EnumResultVo.SUCCESS,"成功",orderId);
            }else{
                return new ResultVo<>(EnumResultVo.DataError,"已经存在",-1l);
            }
        }catch (Exception ex) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            log.error("添加订单消息错误，" + ex.getMessage());
            return new ResultVo<>(EnumResultVo.SystemException,"系统异常",0l);
        }
    }

    /**
     * 批量更新订单
     * @param order
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Long> insertOrder(OrderPddEntity order,Integer shopId) {
        try {
            //****查询订单是否存在****//
            var orderTmp = jdbcTemplate.queryForObject("SELECT id,order_sn,auditStatus FROM dc_pdd_orders WHERE order_sn=? and shopId = ? ", new BeanPropertyRowMapper<>(OrderPddEntity.class), order.getOrderSn(),shopId);
            if(orderTmp.getAuditStatus() != -9 ) {
                //不是未拉取状态
                updateOrder(
                    order.getOrder_status(), order.getTracking_company(), 
                    order.getTracking_number(), order.getShipping_time(), 
                    order.getRefund_status(), order.getAfter_sales_status(), 
                    order.getRemark(), order.getOrderSn(), 
                    order.getLast_ship_time(), shopId);
                    log.info("update pdd orders:"+order.getOrderSn());
                return new ResultVo<>(EnumResultVo.Exist, "订单已经存在，更新");

            } else if(orderTmp.getAuditStatus() == -9){
                //通过订单消息过来的订单数据，删除重新添加
                jdbcTemplate.update("DELETE FROM dc_pdd_orders WHERE order_sn=? and shopId = ? ",order.getOrderSn(),shopId);
                log.info("通过订单消息过来的订单数据，删除重新添加:"+order.getOrderSn());
            }
            // if(orderTmp.getAuditStatus() == -9 ) {
            //     //未拉取状态的订单，插入所有数据

            //     /*****1、添加order*****/
            //     StringBuilder orderPullSQL = new StringBuilder();
            //     orderPullSQL.append("UPDATE dc_pdd_orders");
            //     orderPullSQL.append(" SET ");
            //     orderPullSQL.append(" trade_type=?,");
            //     orderPullSQL.append(" confirm_status=?,");
            //     orderPullSQL.append(" free_sf=?,");
            //     orderPullSQL.append(" group_status=?,");
            //     orderPullSQL.append(" capital_free_discount=?,");
            //     orderPullSQL.append(" seller_discount=?,");
            //     orderPullSQL.append(" platform_discount=?,");
            //     orderPullSQL.append(" remark=?,");
            //     orderPullSQL.append(" updated_at=?,");
            //     orderPullSQL.append(" refund_status=?,");
            //     orderPullSQL.append(" is_lucky_flag=?,");
            //     orderPullSQL.append(" order_status=?,");
            //     orderPullSQL.append(" shipping_time=?,");
            //     orderPullSQL.append(" tracking_number=?,");
            //     orderPullSQL.append(" pay_type=?, ");
            //     orderPullSQL.append(" pay_no=?,");
            //     orderPullSQL.append(" postage=?,");
            //     orderPullSQL.append(" discount_amount=?,");
            //     orderPullSQL.append(" goods_amount=?,");
            //     orderPullSQL.append(" pay_amount=?,");
            //     orderPullSQL.append(" receiver_phone=?,");
            //     orderPullSQL.append(" receiver_name=?,");
            //     orderPullSQL.append(" address=?,");
            //     orderPullSQL.append(" town=?,");
            //     orderPullSQL.append(" city=?,");
            //     orderPullSQL.append(" province=?,");
            //     orderPullSQL.append(" country=?,");
            //     orderPullSQL.append(" created_time=?,");
            //     orderPullSQL.append(" pay_time=?,");
            //     orderPullSQL.append(" confirm_time=?,");
            //     orderPullSQL.append(" receive_time=?,");
            //     orderPullSQL.append(" buyer_memo=?,");
            //     orderPullSQL.append(" after_sales_status=?,");
            //     orderPullSQL.append(" order_confirm_time=?, ");
            //     orderPullSQL.append(" excel_log_id=?, ");
            //     orderPullSQL.append(" tracking_company=?, ");
            //     orderPullSQL.append(" last_ship_time=?, ");
            //     orderPullSQL.append(" nameKey=?, ");
            //     orderPullSQL.append(" phoneKey=?, ");
            //     orderPullSQL.append(" addressKey=?, ");
            //     orderPullSQL.append(" auditStatus=? ");
            //     orderPullSQL.append(" WHERE id=? ");
            //     try {
            //         KeyHolder keyHolder = new GeneratedKeyHolder();
            //         jdbcTemplate.update(new PreparedStatementCreator() {
            //             @Override
            //             public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            //                 PreparedStatement ps = connection.prepareStatement(orderPullSQL.toString(), Statement.RETURN_GENERATED_KEYS);

            //                 ps.setInt(1, order.getTradeType());
            //                 ps.setInt(2, order.getConfirm_status());
            //                 ps.setInt(3, order.getFree_sf());
            //                 ps.setInt(4, order.getGroup_status());
            //                 ps.setDouble(5, order.getCapital_free_discount());
            //                 ps.setDouble(6, order.getSeller_discount());
            //                 ps.setDouble(7, order.getPlatform_discount());
            //                 ps.setString(8, order.getRemark());
            //                 ps.setString(9, order.getUpdated_at());
            //                 ps.setInt(10, order.getRefund_status());
            //                 ps.setInt(11, order.getIs_lucky_flag());
            //                 ps.setInt(12, order.getOrder_status());
            //                 ps.setString(13, order.getShipping_time());
            //                 ps.setString(14, order.getTracking_number());
            //                 ps.setString(15, order.getPay_type());
            //                 ps.setString(16, order.getPay_no());
            //                 ps.setDouble(17, order.getPostage());
            //                 ps.setDouble(18, order.getDiscount_amount());
            //                 ps.setDouble(19, order.getGoods_amount());
            //                 ps.setDouble(20, order.getPay_amount());
            //                 ps.setString(21, order.getReceiver_phone());
            //                 ps.setString(22, order.getReceiver_name());
            //                 ps.setString(23, order.getAddress());
            //                 ps.setString(24, order.getTown());
            //                 ps.setString(25, order.getCity());
            //                 ps.setString(26, order.getProvince());
            //                 ps.setString(27, order.getCountry());
            //                 ps.setString(28, order.getCreated_time());
            //                 ps.setString(29, order.getPay_time());
            //                 ps.setString(30, order.getConfirm_time());
            //                 ps.setString(31, order.getReceive_time());
            //                 ps.setString(32, order.getBuyer_memo());
            //                 ps.setInt(33, order.getAfter_sales_status());
            //                 ps.setLong(34, order.getOrderConfirmTime());
            //                 ps.setInt(35, order.getExcelLogId());
            //                 ps.setString(36, order.getTracking_company());
            //                 ps.setString(37, order.getLast_ship_time());
            //                 ps.setString(38, order.getNameKey());
            //                 ps.setString(39, order.getPhoneKey());
            //                 ps.setString(40, order.getAddressKey());
            //                 ps.setInt(41,orderTmp.getAuditStatus()==-9 ? 0 : orderTmp.getAuditStatus());
            //                 ps.setLong(42, orderTmp.getId());

            //                 return ps;
            //             }
            //         }, keyHolder);

            //         jdbcTemplate.update("delete from dc_pdd_orders_item where order_id=?",orderTmp.getId());

            //         /*****1、添加order_item*****/
            //         String itemSQL = "INSERT INTO dc_pdd_orders_item (order_id,goodsName,goodsImg,goodsNum,goodsSpec,goodsSpecNum,"+
            //         "goodsPrice,quantity,erpGoodsId,erpGoodsSpecId,pdd_good_id,pdd_sku_id) VALUE (?,?,?,?,?,?,?,?,?,?,?,?)";
            //         for (var item : order.getItems()) {
            //             /****查询sku*****/
            //             Integer goodsId = 0;
            //             Integer goodsSpecId = 0;
            //             //查询erp商品规格信息
            //             if (StringUtils.isEmpty(item.getGoodsSpecNum()) == false) {
            //                 try {
            //                     ErpGoodsSpecEntity erpGoodsSpec = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpGoodsSpec + " WHERE specNumber=?", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), item.getGoodsSpecNum());
            //                     goodsId = erpGoodsSpec.getGoodsId();
            //                     goodsSpecId = erpGoodsSpec.getId();
            //                 } catch (Exception E) {
            //                 }
            //             }
            //             jdbcTemplate.update(itemSQL, 
            //             orderTmp.getId(), item.getGoodsName(), 
            //             item.getGoodsImg(), item.getGoodsNum(), item.getGoodsSpec(), 
            //             item.getGoodsSpecNum(), item.getGoodsPrice(), item.getQuantity(), 
            //             goodsId, goodsSpecId,item.getPddGoodId(),item.getPddSkuId());
            //         }
            //         log.info("isexist 已存在订单拉取成功："+order.getOrderSn());
            //         return new ResultVo<>(EnumResultVo.SUCCESS,"成功",orderTmp.getId());
            //     }catch (Exception e10){
            //         TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            //         log.error("isexit 已存在订单号，拉取订单错误："+e10.getMessage());
            //         return new ResultVo<>(EnumResultVo.Fail,"失败");
            //     }
            // }
            
        } catch (Exception e) {
           
        }

        log.info("开始新增订单:"+order.getOrderSn());
        //订单不存在，继续往下走
            /*****1、添加order*****/
            StringBuilder orderInsertSQL = new StringBuilder();
            orderInsertSQL.append("INSERT INTO dc_pdd_orders");
            orderInsertSQL.append(" SET ");
            orderInsertSQL.append(" order_sn=?,");
            orderInsertSQL.append(" shopId=?,");
            orderInsertSQL.append(" trade_type=?,");
            orderInsertSQL.append(" confirm_status=?,");
            orderInsertSQL.append(" free_sf=?,");
            orderInsertSQL.append(" group_status=?,");
            orderInsertSQL.append(" capital_free_discount=?,");
            orderInsertSQL.append(" seller_discount=?,");
            orderInsertSQL.append(" platform_discount=?,");
            orderInsertSQL.append(" remark=?,");
            orderInsertSQL.append(" updated_at=?,");
            orderInsertSQL.append(" refund_status=?,");
            orderInsertSQL.append(" is_lucky_flag=?,");
            orderInsertSQL.append(" order_status=?,");
            orderInsertSQL.append(" shipping_time=?,");
            orderInsertSQL.append(" tracking_number=?,");
            orderInsertSQL.append(" pay_type=?, ");
            orderInsertSQL.append(" pay_no=?,");
            orderInsertSQL.append(" postage=?,");
            orderInsertSQL.append(" discount_amount=?,");
            orderInsertSQL.append(" goods_amount=?,");
            orderInsertSQL.append(" pay_amount=?,");
            orderInsertSQL.append(" receiver_phone=?,");
            orderInsertSQL.append(" receiver_name=?,");
            orderInsertSQL.append(" address=?,");
            orderInsertSQL.append(" town=?,");
            orderInsertSQL.append(" city=?,");
            orderInsertSQL.append(" province=?,");
            orderInsertSQL.append(" country=?,");
            orderInsertSQL.append(" created_time=?,");
            orderInsertSQL.append(" pay_time=?,");
            orderInsertSQL.append(" confirm_time=?,");
            orderInsertSQL.append(" receive_time=?,");
            orderInsertSQL.append(" buyer_memo=?,");
            orderInsertSQL.append(" after_sales_status=?,");
            orderInsertSQL.append(" order_confirm_time=?, ");
            orderInsertSQL.append(" excel_log_id=?, ");
            orderInsertSQL.append(" tracking_company=?, ");
            orderInsertSQL.append(" last_ship_time=?, ");
            orderInsertSQL.append(" nameKey=?, ");
            orderInsertSQL.append(" phoneKey=?, ");
            orderInsertSQL.append(" addressKey=? ");

            try {
                KeyHolder keyHolder = new GeneratedKeyHolder();
                jdbcTemplate.update(new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(orderInsertSQL.toString(), Statement.RETURN_GENERATED_KEYS);
                        ps.setString(1, order.getOrderSn());
                        ps.setInt(2,shopId);//来源于dc_shop id
                        ps.setInt(3, order.getTradeType());
                        ps.setInt(4, order.getConfirm_status());
                        ps.setInt(5, order.getFree_sf());
                        ps.setInt(6, order.getGroup_status());
                        ps.setDouble(7, order.getCapital_free_discount());
                        ps.setDouble(8, order.getSeller_discount());
                        ps.setDouble(9, order.getPlatform_discount());
                        ps.setString(10,order.getRemark());
                        ps.setString(11,order.getUpdated_at());
                        ps.setInt(12,order.getRefund_status());
                        ps.setInt(13,order.getIs_lucky_flag());
                        ps.setInt(14,order.getOrder_status());
                        ps.setString(15,order.getShipping_time());
                        ps.setString(16,order.getTracking_number());
                        ps.setString(17,order.getPay_type());
                        ps.setString(18,order.getPay_no());
                        ps.setDouble(19,order.getPostage());
                        ps.setDouble(20,order.getDiscount_amount());
                        ps.setDouble(21,order.getGoods_amount());
                        ps.setDouble(22,order.getPay_amount());
                        ps.setString(23,order.getReceiver_phone());
                        ps.setString(24,order.getReceiver_name());
                        ps.setString(25,order.getAddress());
                        ps.setString(26,order.getTown());
                        ps.setString(27,order.getCity());
                        ps.setString(28,order.getProvince());
                        ps.setString(29,order.getCountry());
                        ps.setString(30,order.getCreated_time());
                        ps.setString(31,order.getPay_time());
                        ps.setString(32,order.getConfirm_time());
                        ps.setString(33,order.getReceive_time());
                        ps.setString(34,order.getBuyer_memo());
                        ps.setInt(35,order.getAfter_sales_status());
                        ps.setLong(36,order.getOrderConfirmTime());
                        ps.setInt(37,order.getExcelLogId());
                        ps.setString(38,order.getTracking_company());
                        ps.setString(39,order.getLast_ship_time());
                        ps.setString(40,order.getNameKey());
                        ps.setString(41,order.getPhoneKey());
                        ps.setString(42,order.getAddressKey());

                        return ps;
                    }
                }, keyHolder);

                Long orderId = keyHolder.getKey().longValue();
                /*****1、添加order_item*****/
                String itemSQL ="INSERT INTO dc_pdd_orders_item (order_id,goodsName,goodsImg,goodsNum,goodsSpec,goodsSpecNum,goodsPrice,quantity,"+
                "erpGoodsId,erpGoodsSpecId,pdd_good_id,pdd_sku_id,itemAmount) VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?)";
                for (var item:order.getItems()) {
                    /****查询sku*****/
                    Integer goodsId = 0;
                    Integer goodsSpecId = 0;
                    /******根据pdd sku id 查找erp goods spec ******/
                    try{
                        var shopGoodsSku = jdbcTemplate.queryForObject("SELECT * FROM dc_shop_goods_sku WHERE skuId=?", new BeanPropertyRowMapper<>(ShopGoodsSkuEntity.class), item.getPddSkuId());
                        goodsId = shopGoodsSku.getErpGoodsId();
                        goodsSpecId = shopGoodsSku.getErpGoodsSpecId();
                        item.setGoodsSpecNum(shopGoodsSku.getErpGoodsSpecCode());
                    }catch(Exception e){}

                    if(goodsId == 0){
                        //用编码查询erp商品规格信息
                        if(StringUtils.isEmpty(item.getGoodsSpecNum())==false) {
                            try {
                                ErpGoodsSpecEntity erpGoodsSpec = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpGoodsSpec + " WHERE specNumber=?", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), item.getGoodsSpecNum());
                                goodsId = erpGoodsSpec.getGoodsId();
                                goodsSpecId = erpGoodsSpec.getId();
                            } catch (Exception E) {
                            }
                        }
                    }
                    
                    jdbcTemplate.update(itemSQL,orderId,item.getGoodsName(),item.getGoodsImg(),item.getGoodsNum(),item.getGoodsSpec(),
                    item.getGoodsSpecNum(),item.getGoodsPrice(),item.getQuantity(),goodsId,goodsSpecId,item.getPddGoodId(),item.getPddSkuId(),item.getItemAmount());
                }
                log.info("new 新订单添加成功："+order.getOrderSn());
                return new ResultVo<>(EnumResultVo.SUCCESS,"成功",orderId);
            }catch (Exception ex){
                String s = order.getNameKey();
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                log.error("更新订单错误信息："+ex.getMessage());
                return new ResultVo<>(EnumResultVo.Fail,"失败");
            }

    }

    public Integer orderCount(String orderSn){
        return jdbcTemplate.queryForObject("SELECT count(0) FROM dc_pdd_orders WHERE order_sn=? ", Integer.class, orderSn);
    }
    /**
     *
     * @param order
     * @param shopId
     * @return
     */
    public ResultVo<Long> editPddOrder(OrderPddEntity order,Integer shopId) {
       try {
            Integer oldOrderCount =jdbcTemplate.queryForObject("SELECT count(0) FROM dc_pdd_orders WHERE order_sn=? ", Integer.class, order.getOrderSn());
            if(oldOrderCount==0){
                insertOrder(order,shopId);
            }else {
                jdbcTemplate.update("update dc_pdd_orders set  order_status=?,tracking_company=?,tracking_number=?,shipping_time=?,refund_status=?,after_sales_status=?,remark=? where order_sn=?  ",
                        order.getOrder_status(), order.getTracking_company(),order.getTracking_number(),order.getShipping_time(),order.getRefund_status(),order.getAfter_sales_status(),order.getRemark(),order.getOrderSn());
/*                if(oldOrderCount>1){
                    jdbcTemplate.update("DELETE dc_pdd_orders,dc_pdd_orders_item from dc_pdd_orders  LEFT JOIN dc_pdd_orders_item  ON dc_pdd_orders.id= dc_pdd_orders_item.order_id WHERE dc_pdd_orders.order_sn=? and dc_pdd_orders.auditStatus=0;",order.getOrderSn());
                }*/
            }
        } catch (Exception e) {

        }
        return new ResultVo<>(EnumResultVo.SUCCESS,"成功");
    }

    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> updateOrder(
        Integer orderStatus,String company,String number,String shipping_time,
        Integer refundStatus,Integer afterStatus,String remark,
        String orderSn,String lastShipTime,Integer shopId) {
            
        try {
            OrderPddEntity order = jdbcTemplate.queryForObject("SELECT * FROM dc_pdd_orders WHERE order_sn=?",new BeanPropertyRowMapper<>(OrderPddEntity.class),orderSn);

            // if(StringUtils.isEmpty(order.getRemark())==false){
            //     remark = remark+order.getRemark();
            // }

            String sql="update dc_pdd_orders set  order_status=?,tracking_company=?,tracking_number=?,shipping_time=?,refund_status=?,after_sales_status=?,remark=?,shopId=?,last_ship_time=? where order_sn=?";
            jdbcTemplate.update(sql,orderStatus,company,number, shipping_time,refundStatus,afterStatus,remark,shopId,lastShipTime,orderSn);
            return new ResultVo<>(EnumResultVo.SUCCESS);
        } catch (Exception e) {
            return new ResultVo<>(EnumResultVo.DataExist, "订单不存在");
        }
    }
/*

    */
/**
     * 系统下单
     * @return
     *//*

    @Transactional(rollbackFor = Exception.class)
    public void editErpSalesOrder(OrderPddEntity salesOrder,Integer shopId){
        ErpSalesPullCountResp resp = new ErpSalesPullCountResp();
        Integer salesOrderId = jdbcTemplate.queryForObject("SELECT IFNULL((SELECT id from erp_sales_order where orderNum=? and shopId=?),0) id",Integer.class,salesOrder.getOrderSn(),shopId);

        StringBuilder insertSQL = new StringBuilder();
        insertSQL.append("INSERT INTO ").append(Tables.ErpSalesOrder);
        insertSQL.append(" set  orderNum=?,");
        insertSQL.append(" goodsCount=?,");
        insertSQL.append(" goodsSpecCount=?,");
        insertSQL.append(" goodsTotalAmount=?,");
        insertSQL.append(" shippingFee=?,");
        insertSQL.append(" totalAmount=?,");
        insertSQL.append(" contactPerson=?,");
        insertSQL.append(" contactMobile=?,");
        insertSQL.append(" province=?,");
        insertSQL.append(" city=?,");
        insertSQL.append(" area=?,");
        insertSQL.append(" address=?,");
        insertSQL.append(" saleType=?,");
        insertSQL.append(" status=?,");
        insertSQL.append(" createBy=?,");
        insertSQL.append(" developerId=?,");
        insertSQL.append(" source=?,");
        insertSQL.append(" shopId=?,");
        insertSQL.append(" buyerUserId=?,");
        insertSQL.append(" payMethod=?,");
        insertSQL.append(" payStatus=?,");
        insertSQL.append(" payTime=?,");
        insertSQL.append(" payAmount=?,");
        insertSQL.append(" orderDate=?,");
        insertSQL.append(" buyerFeedback=?,");
        insertSQL.append(" createOn=?, ");
        insertSQL.append(" sellerMemo=?, ");
        insertSQL.append(" orderTime=?, ");
        insertSQL.append(" buyerName=? ");
        try {
            Integer state = getPddOrderState(salesOrder.getOrder_status(),salesOrder.getRefund_status());
            if(salesOrderId.intValue()==0){
                KeyHolder keyHolder = new GeneratedKeyHolder();
                jdbcTemplate.update(new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(insertSQL.toString(), Statement.RETURN_GENERATED_KEYS);
                        ps.setString(1,salesOrder.getOrderSn());
                        ps.setLong(2,0);
                        ps.setLong(3,salesOrder.getItems().size());
                        ps.setBigDecimal(4,new BigDecimal(salesOrder.getGoods_amount()));
                        ps.setBigDecimal(5,new BigDecimal(salesOrder.getPostage()));
                        ps.setBigDecimal(6,new BigDecimal(salesOrder.getPay_amount()));
                        ps.setString(7,salesOrder.getReceiver_name());
                        ps.setString(8,salesOrder.getReceiver_phone());
                        ps.setString(9,salesOrder.getProvince());
                        ps.setString(10,salesOrder.getCity());
                        ps.setString(11,salesOrder.getTown());
                        ps.setString(12,salesOrder.getAddress());
                        ps.setInt(13,1);
                        ps.setInt(14,state);
                        ps.setString(15,"");//创建人
                        ps.setInt(16,0);
                        ps.setString(17, ErpOrderSourceEnum.PDD.name());
                        ps.setInt(18,shopId);
                        ps.setInt(19,0);//用户id
                        ps.setInt(20,4);//支付方式
                        ps.setInt(21,2);//支付状态
                        ps.setLong(22,DateUtil.dateTimeToStamp(salesOrder.getPay_time()).longValue());
                        ps.setBigDecimal(23,new BigDecimal(salesOrder.getPay_amount()));
                        ps.setString(24,salesOrder.getPay_time());
                        ps.setString(25,salesOrder.getBuyer_memo());
                        ps.setLong(26,salesOrder.getOrderConfirmTime());
                        ps.setString(27,salesOrder.getRemark());
                        ps.setLong(28,salesOrder.getOrderConfirmTime());
                        ps.setString(29,salesOrder.getReceiver_name());
                        return ps;
                    }
                }, keyHolder);

                Long orderId = keyHolder.getKey().longValue();
                //商品总数
                long goodsCount = 0;
                //sku总数
                long goodsSpecCount = 0;
                //添加订单明细
                String itemSQL = "INSERT INTO " + Tables.ErpSalesOrderItem + " (orderId,itemAmount,goodsId,goodsTitle,goodsNumber,goodsImage,skuInfo,specId,specNumber,price,quantity,discountPrice,color,size,originOrderItemId) VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                String erpSpecSQL="select egs.*,eg.`name` goodTitle from "+Tables.ErpGoodsSpec+" egs LEFT JOIN "+Tables.ErpGoods+" eg ON egs.goodsId=eg.id where egs.specNumber=?";
                String orderItemIdSQL="SELECT IFNULL((select id from dc_pdd_orders_item where order_id=(SELECT id FROM dc_pdd_orders WHERE order_sn=?) AND goodsSpecNum=? LIMIT 1),0) id";
                for (var item : salesOrder.getItems()) {
                    ErpGoodsSpecEntity spec=null;
                    try {
                        spec=jdbcTemplate.queryForObject(erpSpecSQL, new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), item.getGoodsSpecNum());
                    }catch (Exception ex){
                        spec=null;
                    }
                    goodsCount += item.getQuantity();
                    goodsSpecCount++;
                    //原订单itemid
                    Long originOrderItemId = jdbcTemplate.queryForObject(orderItemIdSQL,Long.class,salesOrder.getOrderSn(),item.getGoodsSpecNum());
                    //商品规格总价
                    double itemAmount =item.getGoodsPrice()  * item.getQuantity();
                    String specStr=item.getGoodsSpec();
                    String color="";
                    String size="";
                    if(StringUtils.isEmpty(specStr)==false){
                        try {
                            color  = specStr.substring(0, specStr.indexOf(","));

                        }catch (Exception eee){
                            color = specStr;
                        }
                        try {
                            size = specStr.substring(specStr.indexOf(",")+1,specStr.length());
                        }catch (Exception e1){}
                    }

                    Integer goodId=StringUtils.isEmpty(spec) ? 0 :spec.getGoodsId();
                    Integer specId=StringUtils.isEmpty(spec) ? 0 :spec.getId();
                    jdbcTemplate.update(itemSQL, orderId, itemAmount,goodId,item.getGoodsName(),item.getGoodsNum(),item.getGoodsImg(),
                            item.getGoodsSpec(),specId, item.getGoodsSpecNum(), item.getGoodsPrice(), item.getQuantity(),new BigDecimal(0),color,size,originOrderItemId);
                }

                jdbcTemplate.update("update erp_sales_order set goodsCount=?,goodsSpecCount=? where id=?",goodsCount,goodsSpecCount,orderId);
                resp.setAddCount(1);
            }else {
                //更新
                jdbcTemplate.update("update erp_sales_order set status=? where id=?",state,salesOrderId);
                resp.setUpdCount(1);
            }
        }catch (Exception e){
            resp.setFailCount(1);
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        }
    }
*/


    //获取pdd订单状态
    private int getPddOrderState(int pddState,int afterState){
        //erp 0:待提交,1:待审核,2:待发货3:已发货,4:已收货,5:已完成,6:已退款
        //pdd 1：待发货，2：已发货待签收，3：已签收 5：全部
        //pdd 1：无售后或售后关闭，2：售后处理中，3：退款中，4： 退款成功 5：全部
        Integer orderState=2;
        if(afterState==2 || afterState==3 || afterState==4) {
            orderState=6;
        }else if(pddState==1){
            orderState= 2;
        }else if(pddState==2){
            orderState=3;
        }else if(pddState==3){
            orderState=4;
        }
        return orderState;
    }
    /**
     * 新增pdd退货订单列表
     * @return
     */
    @Transactional
    public ResultVo<Integer> editRefundPddOrder(RefundPddEntity r){
        //查询拼多多skuid
        String spec = "";
        List<ShopGoodsSkuEntity> skuList = jdbcTemplate.query("SELECT * FROM dc_shop_goods_sku WHERE skuId=? ",new BeanPropertyRowMapper(ShopGoodsSkuEntity.class),  r.getSkuId());
        if(skuList!=null && skuList.size()>0){
            spec = skuList.get(0).getSpec();
        }
     
        try {
            // if(r.getId().longValue() == 17561619048L)
            // {
            //     String s="";
            // }
            RefundPddEntity refundPddEntity = jdbcTemplate.queryForObject("SELECT id,auditStatus,refund_amount,tracking_number FROM dc_pdd_refund WHERE id=? ",new BeanPropertyRowMapper<>(RefundPddEntity.class),r.getId());
            /****查询sku*****/
            Integer goodsId = 0;
            Integer goodsSpecId = 0;
            String skuInfo="";
            //查询erp商品规格信息
            // if(!StringUtils.isEmpty(r.getSkuNumber())) {
            //     try {
            //         ErpGoodsSpecEntity erpGoodsSpec = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpGoodsSpec + " WHERE specNumber=? limit 1", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), r.getSkuNumber());
            //         goodsId = erpGoodsSpec.getGoodsId();
            //         goodsSpecId = erpGoodsSpec.getId();
            //         skuInfo=erpGoodsSpec.getSizeValue()+" "+erpGoodsSpec.getColorValue();
            //     } catch (Exception E) {
            //     }
            // }
            // r.setGoods_id(goodsId);
            // r.setSkuId(goodsSpecId);
            // r.setSkuInfo(skuInfo);
            String trackingNumber = "";
            if (StringUtils.hasText(r.getTracking_number())){
                trackingNumber = r.getTracking_number();
            }else{
                if (StringUtils.hasText(refundPddEntity.getTracking_number())){
                    trackingNumber = refundPddEntity.getTracking_number();
                }
            }

            StringBuilder insertpddRefundSQL = new StringBuilder();
            insertpddRefundSQL.append("update  dc_pdd_refund");
            insertpddRefundSQL.append(" set id=? ,");
            insertpddRefundSQL.append(" order_sn=?,");
            insertpddRefundSQL.append(" shopId=?,");
            insertpddRefundSQL.append(" after_sales_type=?,");
            insertpddRefundSQL.append(" after_sales_status=?,");
            insertpddRefundSQL.append(" after_sale_reason=?,");
            insertpddRefundSQL.append(" confirm_time=?,");
            insertpddRefundSQL.append(" created_time=?,");
            insertpddRefundSQL.append(" discount_amount=?,");
            insertpddRefundSQL.append(" order_amount=?,");
            insertpddRefundSQL.append(" refund_amount=?,");
            insertpddRefundSQL.append(" goods_image=?,");
            insertpddRefundSQL.append(" goods_id=?,");
            insertpddRefundSQL.append(" goods_name=?,");
            insertpddRefundSQL.append(" goods_number=?,");
            insertpddRefundSQL.append(" sku_number=?,");
            insertpddRefundSQL.append(" sku_info=?,");
            insertpddRefundSQL.append(" quantity=?,");
            insertpddRefundSQL.append(" goods_price=?,");
            insertpddRefundSQL.append(" updated_time=?,");
            insertpddRefundSQL.append(" tracking_number=?, ");
            insertpddRefundSQL.append(" auditStatus=?, ");
            insertpddRefundSQL.append(" skuId=? ");
            insertpddRefundSQL.append(",user_shipping_status=? ");
            insertpddRefundSQL.append(",remark=? ");
            insertpddRefundSQL.append(",shipping_status=? ");
            insertpddRefundSQL.append(" WHERE id=? ");
            //添加pdd退货信息：dc_pdd_refund
            jdbcTemplate.update(insertpddRefundSQL.toString(), r.getId(),
                    r.getOrder_sn(), r.getShopId(), r.getAfter_sales_type(), r.getAfter_sales_status(), r.getAfter_sale_reason(),
                    r.getConfirm_time(), r.getCreated_time(), r.getDiscount_amount(), r.getOrder_amount(), r.getRefund_amount(),
                    r.getGoods_image(), r.getGoodsId(), r.getGoods_name(), r.getGoods_number(), r.getSkuNumber(), spec,
                    r.getQuantity(), r.getGoods_price(), r.getUpdated_time(), trackingNumber,
                    refundPddEntity.getAuditStatus()==-9 ? 0 : refundPddEntity.getAuditStatus(),r.getSkuId(),
                    r.getUserShippingStatus(),r.getRemark(),r.getShippingStatus(),
                    r.getId());
            return new ResultVo<>(EnumResultVo.DataExist, "更新成功");
        }catch (Exception e){
            try {
                /****查询sku*****/
                Integer goodsId = 0;
                Integer goodsSpecId = 0;
                String skuInfo="";
                //查询erp商品规格信息
                // if(!StringUtils.isEmpty(r.getSkuNumber())) {
                //     try {
                //         ErpGoodsSpecEntity erpGoodsSpec = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpGoodsSpec + " WHERE specNumber=? limit 1", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), r.getSkuNumber());
                //         goodsId = erpGoodsSpec.getGoodsId();
                //         goodsSpecId = erpGoodsSpec.getId();
                //         skuInfo=erpGoodsSpec.getSizeValue()+" "+erpGoodsSpec.getColorValue();
                //     } catch (Exception E) {
                //     }
                // }
                // r.setGoods_id(goodsId);
                // r.setSkuId(goodsSpecId);
                // r.setSkuInfo(skuInfo);

                StringBuilder insertpddRefundSQL = new StringBuilder();
                insertpddRefundSQL.append("INSERT INTO dc_pdd_refund");
                insertpddRefundSQL.append(" set id=? ,");
                insertpddRefundSQL.append(" order_sn=?,");
                insertpddRefundSQL.append(" shopId=?,");
                insertpddRefundSQL.append(" after_sales_type=?,");
                insertpddRefundSQL.append(" after_sales_status=?,");
                insertpddRefundSQL.append(" after_sale_reason=?,");
                insertpddRefundSQL.append(" confirm_time=?,");
                insertpddRefundSQL.append(" created_time=?,");
                insertpddRefundSQL.append(" discount_amount=?,");
                insertpddRefundSQL.append(" order_amount=?,");
                insertpddRefundSQL.append(" refund_amount=?,");
                insertpddRefundSQL.append(" goods_image=?,");
                insertpddRefundSQL.append(" goods_id=?,");
                insertpddRefundSQL.append(" goods_name=?,");
                insertpddRefundSQL.append(" goods_number=?,");
                insertpddRefundSQL.append(" sku_number=?,");
                insertpddRefundSQL.append(" sku_info=?,");
                insertpddRefundSQL.append(" quantity=?,");
                insertpddRefundSQL.append(" goods_price=?,");
                insertpddRefundSQL.append(" updated_time=?,");
                insertpddRefundSQL.append(" tracking_number=?, ");
                insertpddRefundSQL.append(" auditStatus=?, ");
                insertpddRefundSQL.append(" skuId=? ");
                insertpddRefundSQL.append(",user_shipping_status=? ");
                insertpddRefundSQL.append(",remark=? ");
                insertpddRefundSQL.append(",shipping_status=? ");

                //添加pdd退货信息：dc_pdd_refund
                jdbcTemplate.update(insertpddRefundSQL.toString(), r.getId(),
                        r.getOrder_sn(), r.getShopId(), r.getAfter_sales_type(), r.getAfter_sales_status(), r.getAfter_sale_reason(),
                        r.getConfirm_time(), r.getCreated_time(), r.getDiscount_amount(), r.getOrder_amount(), r.getRefund_amount(),
                        r.getGoods_image(), r.getGoodsId(), r.getGoods_name(), r.getGoods_number(), r.getSkuNumber(), spec,
                        r.getQuantity(), r.getGoods_price(), r.getUpdated_time(), r.getTracking_number(),0,r.getSkuId(),r.getUserShippingStatus()
                        ,r.getRemark(),r.getShippingStatus());

                return new ResultVo<>(EnumResultVo.SUCCESS, "新增成功");
            }catch (Exception ex){
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                log.info(e.getMessage()+r.getOrder_sn());
                return new ResultVo<>(EnumResultVo.Unable, "未知异常");
            }
        }

/*        try {
            var refundOrder = jdbcTemplate.queryForObject("select id from dc_pdd_refund where id=? ",Long.class,r.getId());
            //更新
            return new ResultVo<>(EnumResultVo.DataExist, "存在");
        }catch (Exception e) {
            return new ResultVo<>(EnumResultVo.SUCCESS, "成功");*/
//        if(r.getAfter_sales_type().intValue() == 2){
//            /************仅退款的订单************/
//            //状态= 2：买家申请退款，待商家处理 3：退货退款，待商家处理 6：驳回退款， 待买家处理
//            if(r.getAfter_sales_status().intValue() !=4 || r.getAfter_sales_status().intValue() !=5){
//                //不是同意的退款
//                return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
//            }
//        }
       // }
    }
    //退款类型
    private Integer getAfterSalesType(int afterSalesType){
        Integer afterType=0;
        if(afterSalesType==2){
            afterType=1;//退款
        }else if(afterSalesType==3){
            afterType=0;//退货退款
        }else afterType=2;//换货
        return afterType;
    }

    /**
     * 更新
     * @param r
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> updRefundPddOrder(RefundPddEntity r){
        try {
            var refundOrder = jdbcTemplate.queryForObject("select * from dc_pdd_refund where id=? ",new BeanPropertyRowMapper<>(RefundPddEntity.class),r.getId());
            // String desc = refundOrder.getDescribe();
            // if(StringUtils.isEmpty(desc)) desc = "";
            // if(StringUtils.isEmpty(r.getDescribe())) r.setDescribe("");
            // desc += r.getDescribe();
            String tracking_number = "";
            if(StringUtils.hasText(r.getTracking_number()) ){
                tracking_number = r.getTracking_number();
            }else{
                if(StringUtils.hasText(refundOrder.getTracking_number()) ){
                    tracking_number = refundOrder.getTracking_number();
                } 
            }
            
            //更新
            jdbcTemplate.update("UPDATE dc_pdd_refund SET after_sales_status=?,tracking_number=?,refund_amount=?,shipping_status=?,`remark`=?,tracking_company=? WHERE id=?",
                    r.getAfter_sales_status(),r.getTracking_number(),r.getRefund_amount(),r.getShippingStatus(),r.getRemark(),r.getTracking_company(),r.getId());

            //买家撤销11 买家逾期未处理12
            // if(r.getAfter_sales_status() == 12 || r.getAfter_sales_status() == 11 || r.getAfter_sales_status() == 9){
            //     jdbcTemplate.update("UPDATE dc_pdd_refund SET auditStatus=1 where id=?",r.getId());
            // }


            //查询退款单是否确认到仓库了
            // if(refundOrder.getAuditStatus().intValue() == 0){
            //     var isExist = jdbcTemplate.queryForObject("SELECT COUNT(0) FROM erp_order_return WHERE source_order_num=? and shopId=?",int.class,refundOrder.getOrder_sn(),refundOrder.getShopId());
            //     if(isExist > 0){
            //         jdbcTemplate.update("UPDATE dc_pdd_refund SET auditStatus=1 where id=?",refundOrder.getId());
            //         log.info("查询到订单处理过，auditStatus更新为1");
            //     }
            // }

            return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
        }catch (Exception e) {
            return new ResultVo<>(EnumResultVo.NotFound, "不存在");
        }
    }


    /**
     * 查询订单信息（2022-10-12）
     * @param pageIndex
     * @param pageSize
     * @param orderSn
     * @param status
     * @param shopId
     * @param startTime
     * @param endTime
     * @return
     */
    @Transactional
    public PagingResponse<OrderPddEntity> getOrderListAndItem(Integer pageIndex, Integer pageSize, String orderSn, Integer status, Integer shopId, Integer startTime, Integer endTime, Integer auditStatus,String pddGoodsId) {
        List<Object> params = new ArrayList<>();
        StringBuilder sb = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS o.*,sh.`name` as shopName,sh.`type` as shopType  ");
        sb.append(" FROM ").append(Tables.DcPddOrder).append(" o ");
        sb.append(" left join dc_shop sh on sh.id=o.shopId ");

        sb.append(" WHERE 1=1 ");
        if(shopId !=null && shopId > 0){
            sb.append(" AND o.shopId=?  ");
            params.add(shopId);
        }
        

        if (StringUtils.isEmpty(orderSn) == false) {
            sb.append(" AND o.order_sn=? ");
            params.add(orderSn);
        }
        
        if (StringUtils.isEmpty(pddGoodsId) == false) {
            sb.append(" AND FIND_IN_SET(?,(SELECT concat(pdd_good_id) FROM dc_pdd_orders_item WHERE order_id=o.id)) ");
            params.add(pddGoodsId);
        }

        if (!StringUtils.isEmpty(status)) {
           
            sb.append(" AND o.order_status = ? ");
            params.add(status);
            
        }
/*        if(!StringUtils.isEmpty(tags) && tags.size()>0){
            sb.append(" AND o.tag REGEXP ?  ");
            StringBuilder str=new StringBuilder("(");
            tags.forEach(tag->str.append(tag).append("|"));
            String tag=str.toString().substring(0,str.toString().length() - 1);
            params.add(tag+")");
        }*/

        //待确认
        if (!StringUtils.isEmpty(auditStatus) ) {
            if(auditStatus == 0){
                sb.append(" AND o.auditStatus = 0  and o.refund_status <> 4 ");
            }else{
                sb.append(" AND o.auditStatus = ? ");
                params.add(auditStatus);
            }
        }

        if ( startTime !=null && startTime >0 ) {
            
            sb.append(" AND o.created_time >= ?");
            params.add(DateUtil.stampToDateTime(startTime.longValue()));
        }
        if ( endTime !=null && endTime >0 ) {
            sb.append(" AND o.created_time <= ?");
            params.add(DateUtil.stampToDateTime(endTime.longValue()));
        }

        sb.append(" ORDER BY o.created_time DESC ");
        sb.append("  LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        var list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(OrderPddEntity.class), params.toArray(new Object[params.size()]));
        int totalSize = getTotalSize();

        if (list != null && list.size() > 0) {
            //查询item
            for (var order : list) {
                order.setItems(jdbcTemplate.query("SELECT * FROM " + Tables.DcPddOrderItem + " WHERE order_id=?", new BeanPropertyRowMapper<>(OrderPddItemEntity.class), order.getId()));
            }
        }
        return new PagingResponse<>(pageIndex, pageSize, totalSize, list);
    }

    /**
     * 查询3个月内订单list
     * @param shopId
     * @param orderStatus
     * @param refundStatus
     * @return
     */
    @Transactional
    public PagingResponse<OrderPddEntity> getOrderListByStatus(Integer pageIndex, Integer pageSize,Integer shopId,Integer orderStatus, Integer refundStatus) {
        List<Object> params = new ArrayList<>();
        StringBuilder sb = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS  ");
        sb.append("id,order_sn FROM dc_pdd_orders ");
        sb.append(" WHERE shopId= ? ");
        sb.append(" AND  confirm_time > DATE_SUB(CURDATE(), INTERVAL 3 MONTH)  ");

        params.add(shopId);
        if(orderStatus != null){
            sb.append(" AND order_status=? ");
            params.add(orderStatus);
        }

    
        if(refundStatus != null && refundStatus > 0){
            sb.append(" AND refund_status=? ");
            params.add(refundStatus);
        }
        // else{
        //     sb.append(" AND refund_status=1 ");
        // }

        sb.append(" ORDER BY id DESC ");
        sb.append("  LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);


        var list = jdbcTemplate.query(sb.toString(),new BeanPropertyRowMapper<>(OrderPddEntity.class),params.toArray(new Object[params.size()]));
        int totalSize = getTotalSize();
        return new PagingResponse<>(pageIndex, pageSize, totalSize, list);

    }
    @Transactional
    public PagingResponse<OrderPddEntity> getOrderList(Integer pageIndex, Integer pageSize, String orderSn, Integer status, Integer shopId, List<Integer> tags, Integer startTime, Integer endTime, Integer auditStatus) {
        List<Object> params = new ArrayList<>();
        StringBuilder sb = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS o.*  ");
        sb.append(" FROM ").append(Tables.DcPddOrder).append(" o ");

        sb.append(" WHERE o.shopId=? ");
        params.add(shopId);

        if (StringUtils.isEmpty(orderSn) == false) {
            sb.append(" AND o.order_sn=? ");
            params.add(orderSn);
        }


        if (!StringUtils.isEmpty(status)) {
            if(status.intValue() == 1) {
                sb.append(" AND o.order_status = ? AND o.refund_status = 1");
                params.add(status);
            }else {
                sb.append(" AND o.order_status = ? ");
                params.add(status);
            }
        }
/*        if(!StringUtils.isEmpty(tags) && tags.size()>0){
            sb.append(" AND o.tag REGEXP ?  ");
            StringBuilder str=new StringBuilder("(");
            tags.forEach(tag->str.append(tag).append("|"));
            String tag=str.toString().substring(0,str.toString().length() - 1);
            params.add(tag+")");
        }*/
        //待确认
        if (!StringUtils.isEmpty(auditStatus) && auditStatus==0) {
            sb.append(" AND (o.auditStatus = 0 AND LENGTH(o.tracking_number)>0) OR (o.auditStatus = 0 AND is_lucky_flag=2) ");
        }
        //待发货
        if (!StringUtils.isEmpty(auditStatus) && auditStatus==1) {
            sb.append(" AND (o.auditStatus = 1  o.send_status=4 and o.refund_status=1 ) ");
        }
        if ( startTime !=null && startTime >0 ) {
            sb.append(" AND o.order_confirm_time >= ?");
            params.add(startTime);
        }
        if ( endTime !=null && endTime >0 ) {
            sb.append(" AND o.order_confirm_time <= ?");
            params.add(endTime);
        }

        sb.append(" ORDER BY o.last_ship_time asc,o.created_time DESC ");
        sb.append("  LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        var list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(OrderPddEntity.class), params.toArray(new Object[params.size()]));
        int totalSize = getTotalSize();

        return new PagingResponse<>(pageIndex, pageSize, totalSize, list);
    }

    /**
     * 获取订单展示列表（order_item left join orders）
     * @param pageIndex
     * @param pageSize
     * @param orderSn
     * @param goodsSpecNum
     * @param status
     * @param shopId
     * @param startTime
     * @param endTime
     * @return
     */
    @Transactional
    public PagingResponse<OrderViewModel> getOrderViewList(Integer pageIndex, Integer pageSize, String orderSn, String goodsSpecNum, Integer status, Integer refundStatus, Integer shopId, Integer startTime, Integer endTime, Integer printStatus, int orderBy, String trackingNumber, String goodsNum) {
        StringBuilder sql = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS ");
        sql.append(" poi.id as itemId,poi.order_id,poi.goodsName,poi.goodsImg,poi.goodsNum,poi.goodsSpec,poi.goodsSpecNum,poi.goodsPrice,poi.quantity,poi.pdd_good_id");
        sql.append(",po.order_sn,po.shopId,po.remark,po.created_time,po.last_ship_time,po.pay_amount,po.shipping_time,po.pay_time");
        sql.append(",po.auditStatus,po.order_status,po.refund_status,po.result,shop.name as shopName ");
        sql.append(",po.receiver_name1,po.receiver_phone1,po.receiver_address1");
        sql.append(",po.tracking_number,po.tracking_company,concat(po.province,po.city,po.town) AS `area`  ");
        sql.append(",(SELECT pr.id FROM dc_pdd_refund pr WHERE pr.order_sn = po.order_sn ORDER BY  pr.id DESC LIMIT 1) AS refundId, ");
        sql.append("(SELECT IFNULL(SUM(currentQty),0)  FROM erp_goods_stock_info WHERE specNumber=poi.goodsSpecNum and isDelete=0) as currentQty, ");
        sql.append("(SELECT IFNULL(SUM(it1.quantity),0) FROM erp_order_item AS it1 WHERE it1.skuNumber=poi.goodsSpecNum AND  it1.`status` <= 1) pickingQty ");
        sql.append(",eg.`name` as goodsNickName ");
        sql.append(" FROM dc_pdd_orders_item AS poi ");
        sql.append(" LEFT JOIN dc_pdd_orders AS po ON po.id=poi.order_id ");
        sql.append(" LEFT JOIN dc_shop_goods pd on pd.goodsId = pdd_good_id ");
        sql.append(" LEFT JOIN erp_goods eg on eg.id = pd.erp_goods_id ");
        sql.append(" LEFT JOIN dc_shop shop on shop.id = po.shopId ");
        sql.append(" WHERE 1=1 ");

        List<Object> params = new ArrayList<>();
        if(!StringUtils.isEmpty(shopId) && shopId>0){
            sql.append(" and  po.shopId=? ");
            params.add(shopId);
        }

        if (StringUtils.isEmpty(orderSn) == false) {
            sql.append(" AND po.order_sn=? ");
            params.add(orderSn);
        }
        if (StringUtils.isEmpty(goodsSpecNum) == false) {
            sql.append(" AND (poi.goodsSpecNum=? or poi.pdd_good_id=? )");
            params.add(goodsSpecNum);
            params.add(goodsSpecNum);
        }
        if(!StringUtils.isEmpty(trackingNumber)){
            sql.append(" AND po.tracking_number=? ");
            params.add(trackingNumber);
        }

        if(printStatus != null){
            if(printStatus.intValue() == 0){
                sql.append(" AND po.order_status = 1 AND po.refund_status = 1 AND po.printStatus=0 ");
            }else if(printStatus.intValue() == 1){
                if(StringUtils.isEmpty(trackingNumber)) {
                    sql.append(" AND po.order_status = 1  AND po.printStatus=1 ");
                }else{
                    sql.append(" AND po.printStatus=1 ");
                }
            }
        }

        // if (!StringUtils.isEmpty(status) && status.intValue() >= 0) {
        //     if(status.intValue() == 1 ) {
        //         sql.append(" AND po.order_status = ? AND po.refund_status = 1");
        //         params.add(status);
        //     }else if(status.intValue() == 2) {
        //         sql.append(" AND po.order_status = ? AND po.refund_status = 1");
        //         params.add(status);
        //     }else {
        //         sql.append(" AND po.order_status = ? ");
        //         params.add(status);
        //     }
        // }
        if(status != null && status.intValue() >= 0){
            sql.append(" AND po.order_status = ? ");
            params.add(status);
        }
        if(refundStatus != null && refundStatus.intValue() >= 0){
            sql.append(" AND po.refund_status =? ");
            params.add(refundStatus);
        }
       if(!StringUtils.isEmpty(goodsNum)) {
            sql.append(" and poi.goodsNum=? ");
            params.add(goodsNum);
        }
        if ( startTime !=null && startTime >0 ) {
            sql.append(" AND po.order_confirm_time >= ?");
            params.add(startTime);
        }
        if ( endTime !=null && endTime >0 ) {
            sql.append(" AND po.order_confirm_time <= ?");
            params.add(endTime);
        }
        if(refundStatus != null && refundStatus.intValue() >= 0){
            sql.append(" AND po.refund_status =? ");
            params.add(refundStatus);
        }

        if(orderBy == 1){
            sql.append(" ORDER BY po.last_ship_time asc  ");
        }else{
            sql.append(" ORDER BY po.created_time DESC ");
        }

        sql.append("  LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        // log.info(sql.toString());
        var list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(OrderViewModel.class), params.toArray(new Object[params.size()]));
        int totalSize = getTotalSize();

        return new PagingResponse<>(pageIndex, pageSize, totalSize, list);
    }

    public PagingResponse<OrderViewModel> getOrderViewListByPrint(Integer pageIndex, Integer pageSize, Integer shopId,Integer printStatus,String trackingNumber,String printStartTime,String printEndTime) {
        StringBuilder sql = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS ");
        sql.append(" poi.order_id,poi.goodsName,poi.goodsImg,poi.goodsNum,poi.goodsSpec,poi.goodsSpecNum,poi.goodsPrice,poi.quantity");
        sql.append(",po.order_sn,po.shopId,po.remark,po.created_time,po.last_ship_time,po.pay_amount");
        sql.append(",po.auditStatus,po.order_status,po.refund_status,po.result,po.print_time ");
        sql.append(",po.tracking_number,po.tracking_company,concat(po.province,po.city,po.town) AS `area` ");
        sql.append(",(SELECT IFNULL(SUM(currentQty),0)  FROM erp_goods_stock_info WHERE specNumber=poi.goodsSpecNum and isDelete=0) as currentQty ");
        sql.append(",(SELECT IFNULL(SUM(it1.quantity),0) FROM erp_order_item AS it1 WHERE it1.skuNumber=poi.goodsSpecNum AND  it1.`status` <= 1) pickingQty ");
        sql.append(" FROM dc_pdd_orders_item AS poi ");
        sql.append(" LEFT JOIN dc_pdd_orders AS po ON po.id=poi.order_id ");

        List<Object> params = new ArrayList<>();

        sql.append(" WHERE po.shopId=? ");
        params.add(shopId);
        if(!StringUtils.isEmpty(trackingNumber)){
            sql.append(" AND po.tracking_number=? ");
            params.add(trackingNumber);
        }

        if(printStatus != null){
            if(printStatus.intValue() == 0){
                sql.append(" AND po.order_status = 1 AND po.refund_status = 1 AND po.printStatus=0 ");
            }else if(printStatus.intValue() == 1){
                if(StringUtils.isEmpty(trackingNumber) && StringUtils.isEmpty(printStartTime)) {
                    sql.append(" AND po.order_status = 1  AND po.printStatus=1 ");
                }else {
                    sql.append(" AND po.printStatus=1 ");
                }
            }
        }

        if(!StringUtils.isEmpty(printStartTime)){
            sql.append(" AND po.print_time >= ?");
            params.add(printStartTime);
        }
        if(!StringUtils.isEmpty(printEndTime)){
            sql.append(" AND po.print_time <= ?");
            params.add(printEndTime);
        }

        sql.append(" ORDER BY po.created_time DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        var list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(OrderViewModel.class), params.toArray(new Object[params.size()]));
        int totalSize = getTotalSize();

        return new PagingResponse<>(pageIndex, pageSize, totalSize, list);
    }
    public PagingResponse<OrderViewModel> getOrderViewListPrintWdy(Integer shopId,Integer pageIndex, Integer pageSize,String orderSn,String goodsNum,String goodsSpecNum,Integer printStatus,String startTime,String endTime) {
        StringBuilder sql = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS ");
        sql.append(" poi.order_id,poi.goodsName,poi.goodsImg,poi.goodsNum,poi.goodsSpec,poi.goodsSpecNum,poi.goodsPrice,poi.quantity");
        sql.append(",po.order_sn,po.shopId,po.remark,po.created_time,po.last_ship_time,po.pay_amount");
        sql.append(",po.auditStatus,po.order_status,po.refund_status,po.result ");
        sql.append(",po.tracking_number,po.tracking_company,concat(po.province,po.city,po.town) AS `area` ");
        sql.append(",(SELECT pr.id FROM dc_pdd_refund pr WHERE pr.order_sn = po.order_sn ORDER BY  pr.id DESC LIMIT 1) AS refundId, ");
        sql.append("(SELECT IFNULL(SUM(currentQty),0)  FROM erp_goods_stock_info WHERE specNumber=poi.goodsSpecNum and isDelete=0) as currentQty, ");
        sql.append("(SELECT IFNULL(SUM(it1.quantity),0) FROM erp_order_item AS it1 WHERE it1.skuNumber=poi.goodsSpecNum AND  it1.`status` <= 1) pickingQty ");
        sql.append(" FROM dc_pdd_orders_item AS poi ");
        sql.append(" LEFT JOIN dc_pdd_orders AS po ON po.id=poi.order_id ");

        List<Object> params = new ArrayList<>();

        sql.append(" WHERE po.shopId=? ");
        params.add(shopId);


        if (StringUtils.isEmpty(orderSn) == false) {
            sql.append(" AND po.order_sn=? ");
            params.add(orderSn);
        }
        if (StringUtils.isEmpty(goodsSpecNum) == false) {
            sql.append(" AND poi.goodsSpecNum=? ");
            params.add(goodsSpecNum);
        }
        if(printStatus != null){
            if(printStatus.intValue() == 0){
                sql.append(" AND po.order_status = 1 AND po.refund_status = 1 AND po.printStatus=0 ");
            }else if(printStatus.intValue() == 1){
                sql.append(" AND po.printStatus=1 ");
            }
        }

        if(!StringUtils.isEmpty(goodsNum)) {
            sql.append(" and poi.goodsNum=? ");
            params.add(goodsNum);
        }
        if (!StringUtils.isEmpty(startTime)) {
            sql.append(" AND po.order_confirm_time >= ?");
            params.add(startTime);
        }
        if (!StringUtils.isEmpty(endTime) ) {
            sql.append(" AND po.order_confirm_time <= ?");
            params.add(endTime);
        }
        sql.append(" ORDER BY po.created_time DESC ");

        sql.append("  LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        var list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(OrderViewModel.class), params.toArray(new Object[params.size()]));
        int totalSize=jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);

       /* StringBuilder sql = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS ");
        sql.append(" poi.order_id,poi.goodsName,poi.goodsImg,poi.goodsNum,poi.goodsSpec,poi.goodsSpecNum,poi.goodsPrice,poi.quantity");
        sql.append(",po.order_sn,po.shopId,po.remark,po.created_time,po.last_ship_time,po.pay_amount");
        sql.append(",po.auditStatus,po.order_status,po.refund_status,po.result ");
        sql.append(",po.tracking_number,po.tracking_company,concat(po.province,po.city,po.town) AS `area` ");
        sql.append(",(SELECT pr.id FROM dc_pdd_refund pr WHERE pr.order_sn = po.order_sn ORDER BY  pr.id DESC LIMIT 1) AS refundId, ");
        sql.append("(SELECT IFNULL(SUM(currentQty),0)  FROM erp_goods_stock_info WHERE specNumber=poi.goodsSpecNum and isDelete=0) as currentQty, ");
        sql.append("(SELECT IFNULL(SUM(it1.quantity),0) FROM erp_order_item AS it1 WHERE it1.skuNumber=poi.goodsSpecNum AND  it1.`status` <= 1) pickingQty ");
        sql.append(" FROM dc_pdd_orders_item AS poi ");
        sql.append(" LEFT JOIN dc_pdd_orders AS po ON po.id=poi.order_id ");

        List<Object> params = new ArrayList<>();

        sql.append(" WHERE 1=1 ");
        if(!StringUtils.isEmpty(shopId)){
            sql.append(" and po.shopId=? ");
            params.add(shopId);
        }

        if(printStatus.intValue() == 0){
            sql.append(" AND po.order_status = 1 AND po.refund_status = 1 AND po.printStatus=0 ");
        }else if(printStatus.intValue() == 1){
            sql.append(" AND po.order_status = 1  AND po.printStatus=1 ");
        }

        if(!StringUtils.isEmpty(goodsNum)) {
            sql.append(" and poi.goodsNum=? ");
            params.add(goodsNum);
        }
        if (StringUtils.isEmpty(orderSn) == false) {
            sql.append(" AND po.order_sn=? ");
            params.add(orderSn);
        }
        if (StringUtils.isEmpty(goodsSpecNum) == false) {
            sql.append(" AND poi.goodsSpecNum=? ");
            params.add(goodsSpecNum);
        }

        if (!StringUtils.isEmpty(startTime)) {
            sql.append(" AND po.order_confirm_time >= ?");
            params.add(startTime);
        }
        if (!StringUtils.isEmpty(endTime) ) {
            sql.append(" AND po.order_confirm_time <= ?");
            params.add(endTime);
        }

        sql.append(" ORDER BY po.last_ship_time DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        var list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(OrderViewModel.class), params.toArray(new Object[params.size()]));
        int totalSize = getTotalSize();*/

        return new PagingResponse<>(pageIndex, pageSize, totalSize, list);
    }
    /**
     * 查询合并待打单
     * @param shopId
     * @return
     */
    public List<OrderPddEntity> getPddOrderHebing(Integer shopId, Integer printStatus,String goodsNum){
        List<Object> params = new ArrayList<>();
        List<OrderPddEntity> list=new ArrayList<>();
        StringBuilder sb=new StringBuilder("select id,nameKey,phoneKey,addressKey from dc_pdd_orders WHERE order_status=1 and refund_status=1 " );

        if(!StringUtils.isEmpty(printStatus)){
            sb.append(" AND printStatus=? ");
            params.add(printStatus);
        }
        sb.append(" AND LENGTH(nameKey)>0   ");
        if(!StringUtils.isEmpty(shopId) && shopId>0){
            sb.append(" and shopId=? ");
            params.add(shopId);
        }
        sb.append(" group by nameKey,phoneKey,addressKey having count(0) > 1 ");

        var hebingList = jdbcTemplate.query(sb.toString(),new BeanPropertyRowMapper<>(OrderPddHebingVo.class),params.toArray(new Object[params.size()]));

        for(var order:hebingList){
            var hebing = jdbcTemplate.query("select *,(select IFNULL(SUM(poi.quantity),0)  from dc_pdd_orders_item poi left join dc_pdd_orders AS po ON po.id=poi.order_id where po.order_status=1 AND po.refund_status=1 and po.printStatus=0 AND LENGTH(po.nameKey)>0 AND po.shopId=? and po.nameKey=? and po.phoneKey=? and po.addressKey=?) totalQty from dc_pdd_orders where id=?",new BeanPropertyRowMapper<>(OrderPddEntity.class),shopId,order.getNameKey(),order.getPhoneKey(),order.getAddressKey(),order.getId());
            List<Object> paramsItem = new ArrayList<>();
            StringBuilder sbItem=new StringBuilder("select poi.*,po.order_sn,po.last_ship_time,po.result, " );
            sbItem.append("(SELECT IFNULL(SUM(currentQty),0)  FROM erp_goods_stock_info WHERE specNumber=poi.goodsSpecNum and isDelete=0) as currentQty, ");
            sbItem.append("(SELECT IFNULL(SUM(it1.quantity),0) FROM erp_order_item AS it1 WHERE it1.skuNumber=poi.goodsSpecNum AND  it1.`status` <= 1) pickingQty ");
            sbItem.append(" from dc_pdd_orders_item poi left join dc_pdd_orders AS po ON po.id=poi.order_id where po.order_status=1 and po.refund_status=1 AND po.printStatus=0 AND LENGTH(po.nameKey)>0 ");
            if(!StringUtils.isEmpty(goodsNum)){
                sbItem.append(" AND poi.goodsNum=? ");
                paramsItem.add(goodsNum);
            }
            if(!StringUtils.isEmpty(shopId) && shopId>0){
                sbItem.append(" and shopId=? ");
                paramsItem.add(shopId);
            }
            sbItem.append(" and po.nameKey=? and po.phoneKey=? and po.addressKey=? ");
            paramsItem.add(order.getNameKey());
            paramsItem.add(order.getPhoneKey());
            paramsItem.add(order.getAddressKey());

            hebing.get(0).setItems(jdbcTemplate.query(sbItem.toString(),new BeanPropertyRowMapper<>(OrderPddItemEntity.class),paramsItem.toArray(new Object[paramsItem.size()])));
            list.add(hebing.get(0));
        }
        return list;
    }
    /**
     * 查询待打单列表
     * @param shopId
     * @return
     */
    public List<OrderPddEntity> getPddOrderCodePrint(Integer shopId){
        return jdbcTemplate.query("select * from dc_pdd_orders where  printStatus=2 AND  LENGTH(tracking_number)>0  ",new BeanPropertyRowMapper<>(OrderPddEntity.class));
    }


    public List<OrderPddEntity> getDshList(Integer shopId,Integer auditStatus,Long logId) {
        if(StringUtils.isEmpty(logId)) return null;
        List<Object> params = new ArrayList<>();
        StringBuilder sb = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS o.*  ");
        sb.append(" FROM ").append(Tables.DcPddOrder).append(" o ");

        sb.append(" WHERE (o.shopId=?  and o.excel_log_id=? ) ");
        params.add(shopId);
        params.add(logId);
/*        if(!StringUtils.isEmpty(tags) && tags.size()>0){
            sb.append(" AND o.tag REGEXP ?  ");
            StringBuilder str=new StringBuilder("(");
            tags.forEach(tag->str.append(tag).append("|"));
            String tag=str.toString().substring(0,str.toString().length() - 1);
            params.add(tag+")");
        }*/
        //待确认
        if (!StringUtils.isEmpty(auditStatus)) {
            sb.append(" AND o.auditStatus = ? ");
            params.add(auditStatus);
        }
/*
        if ( startTime !=null && startTime >0 ) {
            sb.append(" AND o.order_confirm_time >= ?");
            params.add(startTime);
        }
        if ( endTime !=null && endTime >0 ) {
            sb.append(" AND o.order_confirm_time <= ?");
            params.add(endTime);
        }
*/

        /*sb.append(" ORDER BY o.created_time ");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);*/

        return jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(OrderPddEntity.class), params.toArray(new Object[params.size()]));
        //int totalSize = getTotalSize();

        //查询item
/*        for (var order : list) {
            order.setItems(jdbcTemplate.query("SELECT * FROM " + Tables.DcPddOrderItem + " WHERE order_id=?", new BeanPropertyRowMapper<>(OrderPddItemEntity.class), order.getId()));
        }*/
    }

    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }


    /**
     * 获取订单详情
     * @param id
     * @return
     */
    public OrderPddEntity getOrderDetailAndItemsById(Long id){

        StringBuilder sb = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS o.*  ");
        sb.append(" FROM ").append(Tables.DcPddOrder).append(" o ");
        sb.append(" WHERE o.id=? ");

        try {
            var order = jdbcTemplate.queryForObject(sb.toString(), new BeanPropertyRowMapper<>(OrderPddEntity.class), id);
            order.setItems(jdbcTemplate.query("SELECT * FROM " + Tables.DcPddOrderItem + " WHERE order_id=?", new BeanPropertyRowMapper<>(OrderPddItemEntity.class), order.getId()));
            return order;
        }catch (Exception e){
            return null;
        }
    }

    /**
     * 修改订单商品
     * @param orderItemId
     * @param erpGoodSpecId
     * @param quantity
     * @return
     */
    public ResultVo<Long> updOrderSpec(Long orderItemId, Integer erpGoodSpecId, Integer quantity){
        if(orderItemId==null || orderItemId.longValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少orderItemId");
        }

        if(erpGoodSpecId==null || erpGoodSpecId.intValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少erpGoodSpecId");
        }
        if(quantity==null || quantity.intValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少quantity");
        }

        String sql = "SELECT * FROM "+Tables.DcPddOrder+" WHERE id = (SELECT order_id FROM "+Tables.DcPddOrderItem+" WHERE id=? )";
        try {
            var order = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(OrderPddEntity.class),orderItemId);
            if(order.getAuditStatus().intValue()!=0)
                return new ResultVo<>(EnumResultVo.DataError,"订单已经确认，不允许修改");
/*            if(order.getOrder_status().intValue() > EnumPddOrderStatus.HasSend.getIndex() )
                return new ResultVo<>(EnumResultVo.DataError,"订单不是待发货状态，不允许修改");*/

            var orderItem = jdbcTemplate.queryForObject("SELECT * FROM "+Tables.DcPddOrderItem+" WHERE id=?",new BeanPropertyRowMapper<>(OrderPddItemEntity.class),orderItemId);

            /**************************/
            String erpGoodSpecSQL = "SELECT s.*,g.name as goodTitle,g.number as goodsNumber from "+Tables.ErpGoodsSpec+" s LEFT JOIN " + Tables.ErpGoods + " g ON g.id=s.goodsId WHERE s.id=?";
            var erpOrderSpec = jdbcTemplate.queryForObject(erpGoodSpecSQL,new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class),erpGoodSpecId);

            String remark = "修改商品SKU，";

            String newSpec = "";
            if(StringUtils.hasText(erpOrderSpec.getColorValue())){
                newSpec += erpOrderSpec.getColorValue() +",";
            }
            if(StringUtils.hasText(erpOrderSpec.getStyleValue())){
                newSpec += erpOrderSpec.getStyleValue() +",";
            }
            if(StringUtils.hasText(erpOrderSpec.getSizeValue())){
                newSpec += erpOrderSpec.getSizeValue() ;
            }

            remark+="原sku：:{sku:"+orderItem.getGoodsSpecNum()+",specId:"+orderItem.getErpGoodsSpecId()+",spec:"+orderItem.getGoodsSpec()+",num:"+ orderItem.getQuantity() +"}";
            remark+="新sku：:{sku:"+erpOrderSpec.getSpecNumber()+",specId:"+erpOrderSpec.getId()+",spec:"+newSpec+",num:"+ quantity +"}";

            

            jdbcTemplate.update("UPDATE "+Tables.DcPddOrderItem+" SET erpGoodsId=?,erpGoodsSpecId=?,goodsNum=?,goodsSpecNum=?,quantity=?,remark=?,goodsSpec=? WHERE id=?"
                    ,erpOrderSpec.getGoodsId(),erpOrderSpec.getId(),erpOrderSpec.getGoodsNumber(),erpOrderSpec.getSpecNumber(),quantity,remark,newSpec,orderItemId);

            return new ResultVo<>(EnumResultVo.SUCCESS,"成功");
        }catch (Exception e){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，不存在orderItem");
        }
    }

    /**
     * 创建pdd订单
     *
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> orderCreatePdd(OrderPddEntity order) {
        /****查询订单是否存在****/
        Integer count=jdbcTemplate.queryForObject("SELECT count(0) FROM dc_pdd_orders WHERE order_sn=?", Integer.class, order.getOrderSn());
        if(count.intValue()>0)return new ResultVo<>(EnumResultVo.Exist, "订单已经存在");

        if(order.getTradeType() == null ) order.setTradeType(0);
        if(order.getConfirm_status() == null ) order.setConfirm_status(1);
        if(order.getFree_sf() == null ) order.setFree_sf(0);
        if(order.getGroup_status() == null) order.setGroup_status(1);
        if(order.getCapital_free_discount() == null ) order.setCapital_free_discount(0.0);
        if(order.getSeller_discount() == null ) order.setSeller_discount(0.00);
        if(order.getRefund_status() ==  null ) order.setRefund_status(1);
        if(order.getIs_lucky_flag() == null ) order.setIs_lucky_flag(1);
        if(order.getOrder_status() == null ) order.setOrder_status(2);
        if(order.getPostage() == null ) order.setPostage(0.0);
        if(order.getDiscount_amount() == null ) order.setDiscount_amount(0.0);
        if(order.getAfter_sales_status() == null ) order.setAfter_sales_status(0);
        if(order.getOrderConfirmTime() == null ) order.setOrderConfirmTime(0l);
        if(order.getPlatform_discount() == null ) order.setPlatform_discount(0.0);

        /*****1、添加order*****/
        StringBuilder orderInsertSQL = new StringBuilder();
        orderInsertSQL.append("INSERT INTO dc_pdd_orders");
        orderInsertSQL.append(" SET ");
        orderInsertSQL.append(" order_sn=?,");
        orderInsertSQL.append(" shopId=?,");
        orderInsertSQL.append(" trade_type=?,");
        orderInsertSQL.append(" confirm_status=?,");
        orderInsertSQL.append(" free_sf=?,");
        orderInsertSQL.append(" group_status=?,");
        orderInsertSQL.append(" capital_free_discount=?,");
        orderInsertSQL.append(" seller_discount=?,");
        orderInsertSQL.append(" platform_discount=?,");
        orderInsertSQL.append(" remark=?,");
        orderInsertSQL.append(" updated_at=?,");
        orderInsertSQL.append(" refund_status=?,");
        orderInsertSQL.append(" is_lucky_flag=?,");
        orderInsertSQL.append(" order_status=?,");
        orderInsertSQL.append(" shipping_time=?,");
        orderInsertSQL.append(" tracking_number=?,");
        orderInsertSQL.append(" pay_type=?, ");
        orderInsertSQL.append(" pay_no=?,");
        orderInsertSQL.append(" postage=?,");
        orderInsertSQL.append(" discount_amount=?,");
        orderInsertSQL.append(" goods_amount=?,");
        orderInsertSQL.append(" pay_amount=?,");
        orderInsertSQL.append(" receiver_phone=?,");
        orderInsertSQL.append(" receiver_name=?,");
        orderInsertSQL.append(" address=?,");
        orderInsertSQL.append(" town=?,");
        orderInsertSQL.append(" city=?,");
        orderInsertSQL.append(" province=?,");
        orderInsertSQL.append(" country=?,");
        orderInsertSQL.append(" created_time=?,");
        orderInsertSQL.append(" pay_time=?,");
        orderInsertSQL.append(" confirm_time=?,");
        orderInsertSQL.append(" receive_time=?,");
        orderInsertSQL.append(" buyer_memo=?,");
        orderInsertSQL.append(" after_sales_status=?,");
        orderInsertSQL.append(" order_confirm_time=? ");
        KeyHolder keyHolder = new GeneratedKeyHolder();

        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(orderInsertSQL.toString(), Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, order.getOrderSn());
                ps.setInt(2,order.getShopId());//来源于dc_shop id
                ps.setInt(3, order.getTradeType());
                ps.setInt(4, order.getConfirm_status());
                ps.setInt(5, order.getFree_sf());
                ps.setInt(6, order.getGroup_status());
                ps.setDouble(7, order.getCapital_free_discount());
                ps.setDouble(8, order.getSeller_discount());
                ps.setDouble(9, order.getPlatform_discount());
                ps.setString(10,order.getRemark());
                ps.setString(11,order.getUpdated_at());
                ps.setInt(12,order.getRefund_status());
                ps.setInt(13,order.getIs_lucky_flag());
                ps.setInt(14,order.getOrder_status());
                ps.setString(15,order.getShipping_time());
                ps.setString(16,order.getTracking_number());
                ps.setString(17,order.getPay_type());
                ps.setString(18,order.getPay_no());
                ps.setDouble(19,order.getPostage());
                ps.setDouble(20,order.getDiscount_amount());
                ps.setDouble(21,order.getGoods_amount());
                ps.setDouble(22,order.getPay_amount());
                ps.setString(23,order.getReceiver_phone());
                ps.setString(24,order.getReceiver_name());
                ps.setString(25,order.getAddress());
                ps.setString(26,order.getTown());
                ps.setString(27,order.getCity());
                ps.setString(28,order.getProvince());
                ps.setString(29,order.getCountry());
                ps.setString(30,order.getCreated_time());
                ps.setString(31,order.getPay_time());
                ps.setString(32,order.getConfirm_time());
                ps.setString(33,order.getReceive_time());
                ps.setString(34,order.getBuyer_memo());
                ps.setInt(35,order.getAfter_sales_status());
                ps.setLong(36,order.getOrderConfirmTime());

                return ps;
            }
        }, keyHolder);

        Long orderId = keyHolder.getKey().longValue();
        /*****1、添加order_item*****/
        String itemSQL ="INSERT INTO dc_pdd_orders_item (order_id,goodsName,goodsImg,goodsNum,goodsSpec,goodsSpecNum,goodsPrice,quantity,erpGoodsId,erpGoodsSpecId,isGift) VALUE (?,?,?,?,?,?,?,?,?,?,?)";
        for (var item:order.getItems()) {
            if(item.getErpGoodsId() == null ) item.setErpGoodsId(0);
            if(item.getErpGoodsSpecId() == null ) item.setErpGoodsSpecId(0);
            if(item.getIsGift() == null ) item.setIsGift(0);
            jdbcTemplate.update(itemSQL,orderId,item.getGoodsName(),item.getGoodsImg(),item.getGoodsNum(),item.getGoodsSpec(),item.getGoodsSpecNum(),item.getGoodsPrice(),item.getQuantity(),item.getErpGoodsId(),item.getErpGoodsSpecId(),item.getIsGift());
        }
        return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
    }

    /**
     *
     * @param orderId
     * @param erpGoodsId
     * @param erpGoodSpecId
     * @param quantity
     * @return
     */
    public ResultVo<Long> addOrderGift(Long orderId, Integer erpGoodsId, Integer erpGoodSpecId, Integer quantity){
        if(orderId==null || orderId.longValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少orderId");
        }
        if(erpGoodsId==null || erpGoodsId.intValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少erpGoodsId");
        }
        if(erpGoodSpecId==null || erpGoodSpecId.intValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少erpGoodSpecId");
        }
        if(quantity==null || quantity.intValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少quantity");
        }

        String sql = "SELECT * FROM "+Tables.DcPddOrder+" WHERE id =? ";
        try {
            var order = jdbcTemplate.queryForObject(sql,new BeanPropertyRowMapper<>(OrderPddEntity.class),orderId);

            if(order.getAuditStatus().intValue()!=0)
                return new ResultVo<>(EnumResultVo.DataError,"订单已经确认，不允许添加赠品");
            if(order.getOrder_status().intValue() != EnumPddOrderStatus.WaitSend.getIndex() )
                return new ResultVo<>(EnumResultVo.DataError,"订单不是待发货状态，不允许添加赠品");


            /**************************/
            String erpGoodSpecSQL = "SELECT s.*,g.name as goodTitle,g.number as goodsNumber from "+Tables.ErpGoodsSpec+" s LEFT JOIN " + Tables.ErpGoods + " g ON g.id=s.goodsId WHERE s.id=?";
            var erpGoodsSpec = jdbcTemplate.queryForObject(erpGoodSpecSQL,new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class),erpGoodSpecId);

            String remark = "添加赠品"+erpGoodsSpec.getId()+"("+erpGoodsSpec.getSpecNumber()+")数量："+quantity;
            String specName = "";
            if(StringUtils.isEmpty(erpGoodsSpec.getColorValue())==false){
                specName += "颜色："+erpGoodsSpec.getColorValue();
            }
            if(StringUtils.isEmpty(erpGoodsSpec.getSizeValue())==false){
                specName += "尺码："+erpGoodsSpec.getSizeValue();
            }


            jdbcTemplate.update("INSERT INTO  "+Tables.DcPddOrderItem+" SET order_id=?,erpGoodsId=?,erpGoodsSpecId=?,goodsName=?,goodsImg=?,goodsNum=?,goodsSpec=?,goodsSpecNum=?,goodsPrice=0,quantity=?,remark=?,isGift=1"
                    ,orderId
                    ,erpGoodsSpec.getGoodsId(),erpGoodsSpec.getId(),erpGoodsSpec.getGoodTitle(),erpGoodsSpec.getColorImage()
                    ,erpGoodsSpec.getGoodsNumber(),specName,erpGoodsSpec.getSpecNumber(),quantity,remark);

            return new ResultVo<>(EnumResultVo.SUCCESS,"成功");
        }catch (Exception e){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，不存在order");
        }
    }

    /**
     * 更新快递公司id
     *
     * @param express
     */
    public void initExpress(String express) {
        if (StringUtils.isEmpty(express)) return;
        JSONArray array = JSONArray.parseArray(express);
        for (Object s : array) {
            JSONObject obj = JSONObject.parseObject(s.toString());
            Integer logisticsId=obj.getInteger("id");
            String logisticsCompany=obj.getString("logistics_company");
            Integer expressId = jdbcTemplate.queryForObject("SELECT IFNULL((SELECT id FROM express_company where `name`=? ),0)", Integer.class, logisticsCompany);
            if (expressId <= 0)
                jdbcTemplate.update("INSERT INTO express_company SET `name`=?,create_on=unix_timestamp(now()),pdd_id=?", logisticsCompany, logisticsId);
            if (expressId >= 0)
                jdbcTemplate.update("update express_company set pdd_id=? WHERE id=?", logisticsId, expressId);
        }
    }
    //查询快递公司id
    public Integer getPddLogisticsCompanyId(String logisticsCompany){
        try {
            return jdbcTemplate.queryForObject("SELECT pdd_id FROM erp_express_company where (`name`=? OR `code`=?) ", Integer.class, logisticsCompany,logisticsCompany);
        }catch (Exception ex){
            return 0;
        }
    }
    //更新Pdd订单状态
    public void updPddOrderStats(Long orderId){
        jdbcTemplate.update("update dc_pdd_orders set order_status=? where id=?",EnumPddOrderStatus.HasSend.getIndex(),orderId);
    }

    public void updPddOrderStatus(String orderSN, Integer orderStatus, Integer refundStatus) {
        jdbcTemplate.update("update dc_pdd_orders set order_status=?,refund_status=? where order_sn=?",orderStatus,refundStatus,orderSN);
    }

    public void delPddOrderItemIsGift(Long orderItemId){
        jdbcTemplate.update("delete FROM dc_pdd_orders_item where id=? and isGift=1",orderItemId);
    }

    /**
     * 修改订单是否为刷单
     */
    public void updOderTag(UpdOrderTagReq req){
        StringBuilder builder=new StringBuilder();
        for(Integer tag : req.getTags()){
            builder.append(tag).append(",");
        }
        jdbcTemplate.update("update dc_pdd_orders set tag=?,remark=? where id=?",builder.toString(),req.getRemark(),req.getId());
    }

    /**
     * 创建出库单
     * @param startDate
     * @param endDate
     * @return
     */
    public ResultVo<Integer> createStockOurForm(Integer shopId,String startDate,String endDate){
        String pddOrderSQL="select * from dc_pdd_orders where shopId =? and order_status<=? and auditStatus= 0  and (shipping_time>=? AND shipping_time<=?) ";
        //String pddOrderSQL="select * from dc_pdd_orders where shopId =? and order_status<=? and auditStatus= 0  and order_sn='201227-086332705562798' ";
        String pddOrderItemSQL="select * from dc_pdd_orders_item where order_id=?";
        var list = jdbcTemplate.query(pddOrderSQL,new BeanPropertyRowMapper<>(OrderPddEntity.class),shopId,EnumPddOrderStatus.Received.getIndex(),startDate,endDate);
        list.forEach(s->s.setItems(jdbcTemplate.query(pddOrderItemSQL,new BeanPropertyRowMapper<>(OrderPddItemEntity.class),s.getId())));

        for(var order:list){
            /**********1.1、开始插入仓库系统订单表erp_order**********/
            StringBuilder orderInsertSQL = new StringBuilder();
            orderInsertSQL.append("INSERT INTO ").append(Tables.ErpOrder);
            orderInsertSQL.append(" SET ");
            orderInsertSQL.append(" order_num=?,");
            orderInsertSQL.append(" totalAmount=?,");
            orderInsertSQL.append(" shippingFee=?,");
            orderInsertSQL.append(" orderTime=?,");
            orderInsertSQL.append(" createTime=?,");
            orderInsertSQL.append(" modifyTime=?,");
            orderInsertSQL.append(" confirmedTime=?,");
            orderInsertSQL.append(" remark=?,");
            orderInsertSQL.append(" buyerFeedback=?,");
            orderInsertSQL.append(" sellerMemo=?,");
            orderInsertSQL.append(" contactPerson=?,");
            orderInsertSQL.append(" mobile=?,");
            orderInsertSQL.append(" province=?,");
            orderInsertSQL.append(" city=?,");
            orderInsertSQL.append(" area=?,");
            orderInsertSQL.append(" areaCode=?,");
            orderInsertSQL.append(" town=?,");
            orderInsertSQL.append(" townCode=?,");
            orderInsertSQL.append(" address=?,");
            orderInsertSQL.append(" source=?,");
            orderInsertSQL.append(" status=?,");
            orderInsertSQL.append(" logisticsCompany=?,");
            orderInsertSQL.append(" logisticsCode=?,");
            orderInsertSQL.append(" shopId=?");

            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(orderInsertSQL.toString(), Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, order.getOrderSn());
                    ps.setBigDecimal(2, BigDecimal.valueOf(order.getPay_amount()));
                    ps.setBigDecimal(3, BigDecimal.valueOf(order.getPostage()));
                    ps.setLong(4, DateUtil.dateTimeToStamp(order.getCreated_time()));
                    ps.setLong(5, System.currentTimeMillis() / 1000);
                    ps.setLong(6, System.currentTimeMillis() / 1000);
                    ps.setLong(7, System.currentTimeMillis() / 1000);
                    ps.setString(8, "");
                    ps.setString(9, order.getBuyer_memo());
                    ps.setString(10, "");
                    ps.setString(11, order.getReceiver_name());
                    ps.setString(12, order.getReceiver_phone());
                    ps.setString(13, order.getProvince());
                    ps.setString(14, order.getCity());
                    ps.setString(15, order.getTown());
                    ps.setString(16,"");
                    ps.setString(17, "");
                    ps.setString(18, "");
                    ps.setString(19, order.getAddress());//addr.get(0).getProvince() + " " + addr.get(0).getCity() + " " + addr.get(0).getArea() + " " +
                    ps.setString(20, ErpOrderSourceEnum.PDD.getIndex());
                    ps.setInt(21, EnumErpOrderSendStatus.HasSend.getIndex());
                    ps.setString(22,order.getTracking_company());
                    ps.setString(23,order.getTracking_number());
                    ps.setInt(24, order.getShopId());
                    return ps;
                }
            }, keyHolder);

            Long erpOrderId = keyHolder.getKey().longValue();


            /**********2、添加erp_sales_order***********/
            String salesOrderSQL = "INSERT INTO erp_sales_order (orderNum,buyerUserId,buyerName,goodsCount,goodsSpecCount,goodsTotalAmount,shippingFee,totalAmount" +
                    ",contactPerson,contactMobile,province,city,area,address,saleType,source,buyerFeedback,sellerMemo" +
                    ",status,shopId,payMethod,payStatus,payTime,payAmount,orderDate,orderTime,createOn,auditStatus,logisticsCompany,logisticsCompanyCode) " +
                    "VALUE (?,0,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,1,?,?)";

            KeyHolder keyHolderErpSales = new GeneratedKeyHolder();

            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(salesOrderSQL.toString(), Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, order.getOrderSn());
                    ps.setString(2,order.getReceiver_name());
                    ps.setInt(3,0);
                    ps.setInt(4,0);
                    ps.setBigDecimal(5, BigDecimal.valueOf(order.getGoods_amount()));
                    ps.setBigDecimal(6,  BigDecimal.valueOf(order.getPostage()));
                    ps.setBigDecimal(7,  BigDecimal.valueOf(order.getPay_amount()));
                    ps.setString(8,  order.getReceiver_name());
                    ps.setString(9, order.getReceiver_phone());
                    ps.setString(10, order.getProvince());
                    ps.setString(11, order.getCity());
                    ps.setString(12, order.getTown());
                    ps.setString(13, order.getAddress());
                    ps.setInt(14, 1);//saleType
                    ps.setString(15, "PDD");
                    ps.setString(16, order.getBuyer_memo());
                    ps.setString(17, order.getRemark());
                    ps.setInt(18, EnumErpOrderSendStatus.HasSend.getIndex());//订单状态
                    ps.setInt(19, order.getShopId());//店铺id
                    ps.setInt(20, 4);//支付方式（1:微信，2:支付宝，3:线下支付，4:第三方平台，5:0元购）
                    ps.setInt(21,  2);//付款状态（0未付款1部分付款2完全付款）
                    ps.setLong(22, DateUtil.dateTimeToStamp(order.getPay_time()));//支付时间
                    ps.setBigDecimal(23,BigDecimal.valueOf(order.getPay_amount()));//支付金额
                    ps.setObject(24, order.getCreated_time());//订单日期
                    ps.setLong(25, DateUtil.dateTimeToStamp(order.getCreated_time()));//下单时间
                    ps.setLong(26, System.currentTimeMillis() / 1000 );//创建时间
                    ps.setString(27,order.getTracking_company());
                    ps.setString(28,order.getTracking_number());
                    return ps;
                }
            }, keyHolderErpSales);

            Long salesOrderId = keyHolderErpSales.getKey().longValue();


            /******************添加出库单数据 erp_stock_out_form**********************/
            String no = "OUT" + OrderNumberUtils.getOrderIdByTime();
            String formSQL = "INSERT INTO " + Tables.ErpStockOutForm + " (stockOutNo,status,printStatus,createTime,createBy,outType,remark,sourceNo,stockOutUserName) VALUE (?,?,?,?,?,?,?,?,?)";
            String remark = "订单批量拣货，订单ID："+ salesOrderId;
            KeyHolder keyStockHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(formSQL, Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, no);
                    ps.setInt(2, StockOutFormStatusEnum.OUTED.getIndex());
                    ps.setInt(3, 0);
                    ps.setLong(4, System.currentTimeMillis() / 1000);
                    ps.setString(5, "SYSTEM");
                    ps.setInt(6, 1);//出库类型1订单拣货出库2采购退货出库
                    ps.setString(7, remark);
                    ps.setString(8,order.getOrderSn());
                    ps.setString(9,"SYSTEM");
                    return ps;
                }
            }, keyStockHolder);

            Long stockOutId = keyStockHolder.getKey().longValue();

            boolean flag=false;
            for (var item : order.getItems()) {
                var goodsStockInfos = jdbcTemplate.query("SELECT * FROM " + Tables.ErpGoodsStockInfo + " WHERE specId=? AND goodsId=? AND currentQty>=? AND isDelete = 0"
                        , new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class), item.getErpGoodsSpecId(), item.getErpGoodsId(),item.getQuantity());
                if (goodsStockInfos == null || goodsStockInfos.size() == 0) {
                    flag=true;
                    break;
                }
            }
            if(flag){
                jdbcTemplate.update("delete from erp_order where id=?",erpOrderId);
                jdbcTemplate.update("delete from erp_sales_order where id=?",salesOrderId);
                jdbcTemplate.update("delete from erp_stock_out_form where id=?",stockOutId);
            }else {
                //更新pdd确认状态
                jdbcTemplate.update("UPDATE " + Tables.DcPddOrder + " SET auditStatus=1 WHERE id=?",order.getId());

                addStockFormItem(order,stockOutId,salesOrderId,erpOrderId);
            }

        }
        return new ResultVo<>(EnumResultVo.SUCCESS,"成功");
    }

    /**
     * 添加订单明细
     * @param order
     * @param stockOutId
     * @param salesOrderId
     * @param erpOrderId
     */
    public void addStockFormItem(OrderPddEntity order,Long stockOutId,Long salesOrderId,Long erpOrderId){
        for (var item : order.getItems()) {
            var goodsStockInfos = jdbcTemplate.query("SELECT * FROM " + Tables.ErpGoodsStockInfo + " WHERE specId=? AND goodsId=? AND currentQty>=? AND isDelete = 0"
                    , new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class), item.getErpGoodsSpecId(), item.getErpGoodsId(),item.getQuantity());
            var goodsStockInfo=goodsStockInfos.get(0);

            //销售订单明细
            String salesOrderItemSQL = "INSERT INTO erp_sales_order_item " +
                    "(orderId,itemAmount,goodsId,goodsNumber,goodsTitle,goodsImage,skuInfo,color,size,specId,specNumber,quantity,price,originOrderItemId) " +
                    "VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            jdbcTemplate.update(salesOrderItemSQL, salesOrderId, order.getGoods_amount()
                    , item.getErpGoodsId(), item.getGoodsNum(), item.getGoodsName(), item.getGoodsImg(), item.getGoodsSpec(), "", ""
                    , item.getErpGoodsSpecId(), item.getGoodsSpecNum(), item.getQuantity(), item.getGoodsPrice(),item.getId());

            //出库单明细
            jdbcTemplate.update("INSERT INTO erp_stock_out_form_item (formId,itemId,goodsId,specId,locationId,quantity) VALUE (?,?,?,?,?,?)"
                    ,stockOutId,item.getId(),item.getErpGoodsId(),item.getErpGoodsSpecId(),goodsStockInfo.getLocationId(),item.getQuantity());

            //仓库订单明细
            jdbcTemplate.update("insert into erp_order_item (orderId,aliSubItemID,productMallName,productId,productNumber,skuNumber,skuId,productImgUrl,quantity,status,itemAmount,stockOutFormId,outLocationId) " +
                            "value (?,?,?,?,?,?,?,?,?,?,?,?,?)",
                    erpOrderId, item.getId(), item.getGoodsName(), item.getErpGoodsId(), item.getGoodsNum(), item.getGoodsSpecNum(), item.getErpGoodsSpecId(), item.getGoodsImg(), item.getQuantity().longValue(),
                    EnumErpOrderSendStatus.HasSend.getIndex(), order.getGoods_amount(),stockOutId,goodsStockInfo.getLocationId());

            //仓库商品减库存
            jdbcTemplate.update("UPDATE " + Tables.ErpGoodsSpec + " SET currentQty=currentQty-? WHERE id=?", item.getQuantity(), item.getErpGoodsSpecId());

            //仓库商品仓位减库存
            String updErpGoodsStock = "UPDATE " + Tables.ErpGoodsStockInfo + " SET currentQty=currentQty-? WHERE id=?";
            jdbcTemplate.update(updErpGoodsStock, item.getQuantity(), goodsStockInfo.getId());

            StringBuilder remark=new StringBuilder("订单出库,");
            remark.append("店铺ID:").append(order.getShopId());
            remark.append(",订单ID:").append(order.getId());
            remark.append(",订单明细ID:").append(item.getId());
            //添加出库日志
            jdbcTemplate.update("INSERT INTO " + Tables.ErpGoodsStockLogs + " SET goodsId=?,goodsNumber=?,specId=?,specNumber=?,locationId=?,quantity=?,type=?,sourceType=?,sourceId=?,createTime=?,createUserId=?,createUserName=?,remark=?,createOn=? ",
                    item.getErpGoodsId(), item.getGoodsNum(), item.getErpGoodsSpecId(), item.getGoodsSpecNum(),goodsStockInfo.getLocationId(), item.getQuantity(), EnumGoodsStockLogType.OUT.getIndex(), EnumGoodsStockLogSourceType.OrderSend.getIndex(),order.getId(),DateUtil.getCurrentDateTime(),0, "SYSTEM", remark, System.currentTimeMillis() / 1000);
        }
    }

    /**
     * 获取pdd更新时间
     * @param shopId
     * @return
     */
    public String getPddUpTodate(Integer shopId){
        try {
            return jdbcTemplate.queryForObject("select created_time from dc_pdd_orders where shopId=? ORDER BY id DESC LIMIT 1",String.class,shopId);
        }catch (Exception e){
            return null;
        }
    }

    /**
     * 查询订单统计数据
     * @param date
     * @return
     */
    public PddOrderStatisVo getOrderStatic(String date){
        PddOrderStatisVo statisVo = new PddOrderStatisVo();
        Integer orderCount=0;
        BigDecimal totalPrice=new BigDecimal(0);
        try {
            orderCount=jdbcTemplate.queryForObject("SELECT COUNT(0) from dc_pdd_orders WHERE order_status<=3 AND DATE_FORMAT(created_time,'%Y-%m-%d')=? ",Integer.class,date);
            var list = jdbcTemplate.query("SELECT i.goodsSpec,i.goodsSpecNum,SUM(i.quantity) quantity, SUM(i.goodsPrice*i.quantity) price,count(o.id) orderCount from dc_pdd_orders_item i LEFT JOIN dc_pdd_orders o ON i.order_id=o.id WHERE o.order_status<=3 AND DATE_FORMAT(o.created_time,'%Y-%m-%d')=? GROUP BY i.goodsSpecNum",new BeanPropertyRowMapper<>(PddOrderStatis.class),date);
            totalPrice=jdbcTemplate.queryForObject("SELECT SUM(i.goodsPrice*i.quantity) price from dc_pdd_orders_item i LEFT JOIN dc_pdd_orders o ON i.order_id=o.id WHERE o.order_status<=3 AND DATE_FORMAT(o.created_time,'%Y-%m-%d')=? ",BigDecimal.class,date);
            statisVo.setOrderCount(orderCount);
            statisVo.setTotalPrice(totalPrice);
            statisVo.setItems(list);
        }catch (Exception e){

        }
        return statisVo;
    }

    public List<OrderPddEntity> getPddRefundList(Integer shopId,String startDate,String endDate){
        var list = jdbcTemplate.query("select id,order_sn from dc_pdd_orders where shopId=? and refund_status>1 and (created_time>=? and created_time<=?)", new BeanPropertyRowMapper<>(OrderPddEntity.class), shopId,startDate,endDate);
        //查询item
        for (var order : list) {
            order.setItems(jdbcTemplate.query("SELECT * FROM " + Tables.DcPddOrderItem + " WHERE order_id=?", new BeanPropertyRowMapper<>(OrderPddItemEntity.class), order.getId()));
        }
        return list;
    }
    public OrderPddEntity getOrder(String orderNo){
        try {
            var order = jdbcTemplate.queryForObject("select o.*,(select sum(quantity) FROM dc_pdd_orders_item WHERE order_id=o.id ) as totalQty from dc_pdd_orders o where o.order_sn=? limit 1",new BeanPropertyRowMapper<>(OrderPddEntity.class),orderNo);
            order.setItems(jdbcTemplate.query("select * from dc_pdd_orders_item where order_id=?",new BeanPropertyRowMapper<>(OrderPddItemEntity.class),order.getId()));
            return order;
        }catch (Exception e){
            return null;
        }
    }

    /*public PagingResponse<OrderPddEntity> getDfhList(Integer pageIndex, Integer pageSize, Integer startTime, Integer endTime, Integer shopId,Integer printStatus) {
        List<Object> params = new ArrayList<>();
        StringBuilder sb = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS o.*  ");
        sb.append(" FROM ").append(Tables.DcPddOrder).append(" o ");

        sb.append(" WHERE  o.shopId=? ");
        params.add(shopId);
        if (startTime !=null && startTime >0 ) {
            sb.append(" AND o.order_confirm_time >= ?");
            params.add(startTime);
        }
        if ( endTime !=null && endTime >0 ) {
            sb.append(" AND o.order_confirm_time <= ?");
            params.add(endTime);
        }
        if(!StringUtils.isEmpty(printStatus)){
            if(printStatus==0){
                sb.append(" and o.refund_status=1 and o.order_status=1 and LENGTH(o.tracking_number)=0 ");
            }else {
                sb.append(" and o.printStatus=? ");
                params.add(printStatus);
            }
        }
        sb.append(" ORDER BY o.last_ship_time asc,o.created_time asc  LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        var list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(OrderPddEntity.class), params.toArray(new Object[params.size()]));
        int totalSize = getTotalSize();
        for (var order : list) {
            order.setItems(jdbcTemplate.query("SELECT * FROM " + Tables.DcPddOrderItem + " WHERE order_id=?", new BeanPropertyRowMapper<>(OrderPddItemEntity.class), order.getId()));
        }
        return new PagingResponse<>(pageIndex, pageSize, totalSize, list);
    }
*/
    /**
     * 更新订单打印信息
     * @param company
     * @param code
     * @param encryptedData
     * @param signature
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> updPddOrderPrint(OrderViewModel order,String company,String code,String encryptedData,String signature){
        try {
            //1.更新订单状态
            jdbcTemplate.update("update dc_pdd_orders set tracking_company=?,tracking_number=?,encryptedData=?,signature=?,printStatus=2,auditStatus=1,result=? where id=?", company, code, encryptedData, signature,"已取号",order.getOrderId());

            return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, "失败" + e.getMessage());
        }

    }
    /**
     * 检查订单库存
     * @param orders
     * @return
     */
    public ResultVo<String> checkPrint(List<OrderViewModel> orders){
        StringBuilder sb=new StringBuilder("");
        List<Long> orderIdList=new ArrayList<>();
        for (var item : orders) {
            orderIdList.add(item.getOrderId());
            sb.append(item.getGoodsSpecNum());
            sb.append("("+item.getGoodsSpec()+")");
            sb.append(item.getQuantity()).append("件").append("\\n");
        }
        StringBuilder qtySql=new StringBuilder("SELECT SUM(oi.quantity) AS quantity ");
        qtySql.append(",(SELECT SUM(currentQty) FROM erp_goods_stock_info WHERE specId=oi.erpGoodsSpecId AND isDelete=0) AS currentQty ");
        qtySql.append(",IFNULL((SELECT SUM(i.quantity)  from dc_pdd_orders_item i LEFT JOIN dc_pdd_orders d ON i.order_id=d.id WHERE printStatus=2 and i.erpGoodsSpecId=oi.erpGoodsSpecId),0) as printQty ");
        qtySql.append(",(SELECT SUM(lockedQty) FROM erp_goods_stock_info WHERE specId=oi.erpGoodsSpecId AND isDelete=0) AS lockedQty ");
        qtySql.append(" FROM dc_pdd_orders_item oi WHERE oi.order_id IN (:ids) GROUP BY oi.erpGoodsSpecId ");

        Map<String, Object> args = new HashMap<>();
        args.put("ids", orderIdList);
        NamedParameterJdbcTemplate givenParamJdbcTemp = new NamedParameterJdbcTemplate(jdbcTemplate);

        List<ErpOrderItemEntity> itemList = givenParamJdbcTemp.query(qtySql.toString(), args, new BeanPropertyRowMapper<>(ErpOrderItemEntity.class));
        /************判断订单商品库存************/
        for (var item : itemList) {
            if(StringUtils.isEmpty(item.getCurrentQty())) return new ResultVo<>(EnumResultVo.DataError, item.getSkuNumber() + "商品库存未查询到！");
            //查询库存
            long keyongQty = item.getCurrentQty() - item.getLockedQty()-item.getPrintQty();
            if (keyongQty < item.getQuantity().longValue()) {
                jdbcTemplate.update("UPDATE " + Tables.DcPddOrder + " SET result=?,printStatus=0 WHERE id=?","可用库存不足，不能操作",item.getOrderId());
                return new ResultVo<>(EnumResultVo.DataError, item.getSkuNumber() + "可用库存不足，不能操作");
            }
        }
        return new ResultVo<>(EnumResultVo.SUCCESS, "成功",sb.toString());
    }

    /**
     * 统计待发货商品规格信息
     * @return
     */
    public List<WaitSendGoodsSpecModel> getWaitSendGoodsSpecList(){
        String sql = "SELECT   \n" +
                "poi.goodsNum,poi.goodsSpec,poi.goodsSpecNum,SUM(poi.quantity) as quantity\n" +
                ",IFNULL((SELECT SUM(currentQty) FROM erp_goods_stock_info WHERE specNumber = poi.goodsSpecNum),0) AS currentQty\n" +
                ",IFNULL((SELECT SUM(it1.quantity) FROM erp_order_item  AS it1 WHERE it1.skuNumber=poi.goodsSpecNum AND  it1.`status` <= 1 ),0) AS pickingQty "+
                "\n" +
                "FROM dc_pdd_orders_item AS poi  \n" +
                "LEFT JOIN dc_pdd_orders AS po ON po.id=poi.order_id \n" +
                " WHERE  po.order_status = 1 AND po.refund_status = 1 \n" +
                " \n" +
                " GROUP BY poi.goodsSpecNum ";//ORDER BY quantity DESC
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(WaitSendGoodsSpecModel.class));
    }
    /**
     * 打单list
     * @param orderNo
     * @param isHebing
     * @return
     */
    public ResultVo<PddPrintOrderVo>  getPrintOrderList(String orderNo,Integer isHebing,String goodsNum){
        PddPrintOrderVo resultVo=new PddPrintOrderVo();
        List<OrderViewModel> orders=new ArrayList<>();
        String sql="SELECT poi.order_id,poi.goodsName,poi.goodsImg,poi.goodsNum,poi.goodsSpec,poi.goodsSpecNum,poi.goodsPrice,poi.quantity,po.order_sn,po.shopId,po.remark,po.encryptedData,po.signature,po.province,po.city,po.town,po.address,po.receiver_name,po.receiver_phone,po.nameKey,po.phoneKey,po.addressKey,poi.id as itemId,po.created_time FROM dc_pdd_orders_item AS poi LEFT JOIN dc_pdd_orders AS po ON po.id=poi.order_id WHERE po.order_sn=? ";
        OrderViewModel order= jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(OrderViewModel.class),orderNo).get(0);
        if(isHebing==0) orders.add(order);
        if(isHebing==1){
            List<Object> paramsItem = new ArrayList<>();
            StringBuilder sbItem=new StringBuilder("SELECT poi.order_id,poi.goodsName,poi.goodsImg,poi.goodsNum,poi.goodsSpec,poi.goodsSpecNum,poi.goodsPrice,poi.quantity,po.order_sn,po.shopId,po.remark,po.encryptedData,po.signature,po.province,po.city,po.town,po.address,po.receiver_name,po.receiver_phone,po.nameKey,po.phoneKey,po.addressKey,poi.id as itemId,po.created_time FROM dc_pdd_orders_item AS poi LEFT JOIN dc_pdd_orders AS po ON po.id=poi.order_id WHERE po.order_status=1 and po.refund_status=1 AND po.printStatus=0  AND LENGTH(po.nameKey)>0 " );

            if(!StringUtils.isEmpty(goodsNum)){
                sbItem.append(" AND poi.goodsNum=? ");
                paramsItem.add(goodsNum);
            }
            sbItem.append(" and po.shopId=? and po.nameKey=? and po.phoneKey=? and po.addressKey=?");
            paramsItem.add(order.getShopId());
            paramsItem.add(order.getNameKey());
            paramsItem.add(order.getPhoneKey());
            paramsItem.add(order.getAddressKey());

            orders = jdbcTemplate.query(sbItem.toString(),new BeanPropertyRowMapper<>(OrderViewModel.class),paramsItem.toArray(new Object[paramsItem.size()]));
            if(orders.size()<=1)return new ResultVo<>(EnumResultVo.DataError, "未查询到符号条件合并的订单");
        }
        resultVo.setOrders(orders);

        StringBuilder sb=new StringBuilder("");
        List<Long> orderIdList=new ArrayList<>();
        for (var item : orders) {
            orderIdList.add(item.getOrderId());
            sb.append(item.getGoodsSpecNum());
            sb.append("("+item.getGoodsSpec()+")");
            sb.append(item.getQuantity()).append("件").append("\\n");

            if(StringUtils.isEmpty(item.getGoodsSpecNum())){
                jdbcTemplate.update("UPDATE " + Tables.DcPddOrder + " SET result=?,printStatus=0 WHERE id=?","订单商品SKU不存在",item.getOrderId());
                return new ResultVo<>(EnumResultVo.DataError, "订单商品没有SKU信息");
            }
            ErpGoodsSpecEntity erpGoodsSpec = null;
            try {
                erpGoodsSpec = jdbcTemplate.queryForObject("select spec.*,eg.number as goodsNumber,eg.erpContactId from erp_goods_spec spec LEFT JOIN erp_goods eg ON spec.goodsId=eg.id where spec.specNumber=? LIMIT 1", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), item.getGoodsSpecNum());
            } catch (Exception E) {
                jdbcTemplate.update("UPDATE " + Tables.DcPddOrder + " SET result=?,printStatus=0 WHERE id=?","SKU【" + item.getGoodsSpecNum() + "】不存在",item.getOrderId());
                return new ResultVo<>(EnumResultVo.DataError, "SKU【" + item.getGoodsSpecNum() + "】不存在");
            }
            Integer stockCount = jdbcTemplate.queryForObject("select count(0) from erp_goods_stock_info where specId=? and isDelete=0 limit 1",Integer.class,erpGoodsSpec.getId());
            if(stockCount.intValue()==0){
                jdbcTemplate.update("UPDATE " + Tables.DcPddOrder + " SET result=?,printStatus=0 WHERE id=?","仓库查询不到该商品库存",item.getOrderId());
                return new ResultVo<>(EnumResultVo.DataError, "仓库查询不到该SKU【" + item.getGoodsSpecNum() + "】库存");
            }
            String updItemSQL="update dc_pdd_orders_item set goodsNum=?,goodsSpec=?,erpGoodsId=?,erpGoodsSpecId=? where id=?";
            jdbcTemplate.update(updItemSQL,erpGoodsSpec.getGoodsNumber(),erpGoodsSpec.getColorValue()+erpGoodsSpec.getSizeValue(),erpGoodsSpec.getGoodsId(),erpGoodsSpec.getId(),item.getItemId());
        }
        resultVo.setRemark(sb.toString());
        try {
            StringBuilder qtySql=new StringBuilder("SELECT SUM(oi.quantity) AS quantity,oi.goodsSpecNum as skuNumber ");
            qtySql.append(", IFNULL((SELECT IFNULL(SUM(currentQty),0) FROM erp_goods_stock_info WHERE specId=oi.erpGoodsSpecId AND isDelete=0),0) AS currentQty ");
            qtySql.append(", IFNULL((SELECT SUM(i.quantity)  from dc_pdd_orders_item i LEFT JOIN dc_pdd_orders d ON i.order_id=d.id WHERE printStatus=2 and i.erpGoodsSpecId=oi.erpGoodsSpecId),0) as printQty ");
            qtySql.append(", IFNULL((SELECT SUM(it1.quantity) FROM erp_order_item AS it1 WHERE it1.skuId=oi.erpGoodsSpecId AND  it1.`status` <= 1 ),0) AS pickingQty ");
            qtySql.append(" FROM dc_pdd_orders_item oi WHERE oi.order_id IN (:ids) GROUP BY oi.erpGoodsSpecId ");

            Map<String, Object> args = new HashMap<>();
            args.put("ids", orderIdList);
            NamedParameterJdbcTemplate givenParamJdbcTemp = new NamedParameterJdbcTemplate(jdbcTemplate);

            List<ErpOrderItemEntity> itemList = givenParamJdbcTemp.query(qtySql.toString(), args, new BeanPropertyRowMapper<>(ErpOrderItemEntity.class));
            /************判断订单商品库存************/
            for (var item : itemList) {
                log.info(item.getSkuNumber()+"库存："+item.getCurrentQty()+"拣货："+item.getPickingQty());
                //查询库存
                long keyongQty = item.getCurrentQty() - item.getPickingQty()-item.getPrintQty();
                if (keyongQty < item.getQuantity().longValue()) {
                    jdbcTemplate.update("UPDATE " + Tables.DcPddOrder + " SET result=?,printStatus=0 WHERE id=?","可用库存不足，不能操作",item.getOrderId());
                    return new ResultVo<>(EnumResultVo.DataError, item.getSkuNumber() + "可用库存不足，不能操作");
                }
            }
        }catch (Exception e){
            return new ResultVo<>(EnumResultVo.DataError, "查询订单库存异常");

        }
        return new ResultVo<>(EnumResultVo.SUCCESS,"成功",resultVo);
    }
    /**
     * 查询合并选择订单
     * @param orderSnArr
     * @return
     */
    public ResultVo<List<OrderViewModel>>  getPrintOrderSelectList(ArrayList orderSnArr){
        List<OrderViewModel> orders=new ArrayList<>();
        for(var orderSn:orderSnArr){
            String orderSQL="SELECT poi.order_id,poi.goodsName,poi.goodsImg,poi.goodsNum,poi.goodsSpec,poi.goodsSpecNum,poi.goodsPrice,poi.quantity,po.order_sn,po.shopId,po.remark,po.encryptedData,po.signature,po.province,po.city,po.town,po.address,po.receiver_name,po.receiver_phone,po.nameKey,po.phoneKey,po.addressKey,poi.id as itemId FROM dc_pdd_orders_item AS poi LEFT JOIN dc_pdd_orders AS po ON po.id=poi.order_id WHERE po.order_status=1 and po.refund_status=1 AND po.order_sn=?";
            var ordersTemp = jdbcTemplate.query(orderSQL,new BeanPropertyRowMapper<>(OrderViewModel.class),orderSn);
            if(ordersTemp.size()==0)continue;

            if(StringUtils.isEmpty(ordersTemp.get(0).getGoodsSpecNum())){
                jdbcTemplate.update("UPDATE " + Tables.DcPddOrder + " SET result=?,printStatus=0 WHERE order_sn=?","订单商品SKU不存在",orderSn);
                return new ResultVo<>(EnumResultVo.DataError, "订单商品没有SKU信息");
            }
            //查询erp商品规格信息
            ErpGoodsSpecEntity erpGoodsSpec = null;
            try {
                erpGoodsSpec = jdbcTemplate.queryForObject("select spec.*,eg.number as goodsNumber,eg.erpContactId from erp_goods_spec spec LEFT JOIN erp_goods eg ON spec.goodsId=eg.id where spec.specNumber=? LIMIT 1", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class),ordersTemp.get(0).getGoodsSpecNum());
            } catch (Exception E) {
                jdbcTemplate.update("UPDATE " + Tables.DcPddOrder + " SET result=?,printStatus=0 WHERE order_sn=?","SKU【" + ordersTemp.get(0).getGoodsSpecNum() + "】不存在",orderSn);
                return new ResultVo<>(EnumResultVo.DataError, "SKU【" + ordersTemp.get(0).getGoodsSpecNum() + "】不存在");
            }
            String updItemSQL="update dc_pdd_orders_item set goodsNum=?,goodsSpec=?,erpGoodsId=?,erpGoodsSpecId=? where id=?";
            jdbcTemplate.update(updItemSQL,erpGoodsSpec.getGoodsNumber(),erpGoodsSpec.getColorValue()+erpGoodsSpec.getSizeValue(),erpGoodsSpec.getGoodsId(),erpGoodsSpec.getId(),ordersTemp.get(0).getItemId());
            orders.add(ordersTemp.get(0));
        }
        return new ResultVo<>(EnumResultVo.SUCCESS,"成功",orders);
    }

    /**
     * 重复打印订单
     * @param orderSn
     * @return
     */
    public List<OrderViewModel>  getPrintOrderRepeat(String orderSn){
        var orders= jdbcTemplate.query("select * from dc_pdd_orders where order_sn=?",new BeanPropertyRowMapper<>(OrderPddEntity.class),orderSn);
        var order=orders.get(0);
        StringBuilder codeSql =new StringBuilder("SELECT poi.order_id,poi.goodsName,poi.goodsImg,poi.goodsNum,poi.goodsSpec,poi.goodsSpecNum,poi.goodsPrice,poi.quantity,po.order_sn,po.shopId,po.remark,po.encryptedData,po.signature,po.province,po.city,po.town,po.address,po.receiver_name,po.receiver_phone,po.nameKey,po.phoneKey,po.addressKey,poi.id as itemId,po.tracking_number,po.tracking_company  ");
        codeSql.append(", (SELECT l.number from erp_goods_stock_info s LEFT JOIN erp_stock_location l on  s.locationId=l.id WHERE s.isDelete=0 and s.specId=poi.erpGoodsSpecId limit 1) as locationNumber ");
        codeSql.append("  FROM dc_pdd_orders_item AS poi LEFT JOIN dc_pdd_orders AS po ON po.id=poi.order_id ");
        codeSql.append("  WHERE po.tracking_number=? and  po.signature=? ");
        return jdbcTemplate.query(codeSql.toString(),new BeanPropertyRowMapper<>(OrderViewModel.class),order.getTracking_number(),order.getSignature());
    }
    public List<OrderViewModel> getPrintOrderRepeatNumber(String trackingNumber){
        var orders= jdbcTemplate.query("select * from dc_pdd_orders where tracking_number=? limit 1",new BeanPropertyRowMapper<>(OrderPddEntity.class),trackingNumber);
        var order=orders.get(0);
        StringBuilder codeSql =new StringBuilder("SELECT poi.order_id,poi.goodsName,poi.goodsImg,poi.goodsNum,poi.goodsSpec,poi.goodsSpecNum,poi.goodsPrice,poi.quantity,po.order_sn,po.shopId,po.remark,po.encryptedData,po.signature,po.province,po.city,po.town,po.address,po.receiver_name,po.receiver_phone,po.nameKey,po.phoneKey,po.addressKey,poi.id as itemId,po.tracking_number,po.tracking_company  ");
        codeSql.append(", (SELECT l.number from erp_goods_stock_info s LEFT JOIN erp_stock_location l on  s.locationId=l.id WHERE s.isDelete=0 and s.specId=poi.erpGoodsSpecId limit 1) as locationNumber ");
        codeSql.append("  FROM dc_pdd_orders_item AS poi LEFT JOIN dc_pdd_orders AS po ON po.id=poi.order_id ");
        codeSql.append("  WHERE po.tracking_number=? and  po.signature=? ");
        return jdbcTemplate.query(codeSql.toString(),new BeanPropertyRowMapper<>(OrderViewModel.class),order.getTracking_number(),order.getSignature());
    }

    /**
     * 修改拼多碰订单处理结果
     * @param orderId
     * @param result
     */
    public void updPddOrderResult(Long orderId,String result,Integer printStatus,String printTime){
        List<Object> params = new ArrayList<>();
        StringBuilder sb =new StringBuilder("update dc_pdd_orders set result=?,printStatus=? ");
        params.add(result);
        params.add(printStatus);
        if(!StringUtils.isEmpty(printTime)){
            sb.append(",print_time=? ");
            params.add(printTime);
        }
        sb.append("where id=?");
        params.add(orderId);
        jdbcTemplate.update(sb.toString(),params.toArray(new Object[params.size()]));
    }
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> cancelOrderPrint(Long orderId){
        OrderPddEntity order = null;
        try {
            //查询关联的订单
            order = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.DcPddOrder + " WHERE id=?", new BeanPropertyRowMapper<>(OrderPddEntity.class), orderId);
        } catch (Exception e) {
            return new ResultVo<>(EnumResultVo.NotFound, "订单不存在");//订单不存在return 404;
        }
        try {
            //查询仓库订单，未出库删除。
            var erpOrders = jdbcTemplate.query("select * from erp_order where order_num=?",new BeanPropertyRowMapper<>(ErpOrderEntity.class),order.getOrderSn());
            if(erpOrders.size()==0){
                jdbcTemplate.update("update dc_pdd_orders set printStatus=3 where id=?",orderId);
                return new ResultVo<>(EnumResultVo.SUCCESS,"成功");
            }
            if(erpOrders.size()>0){
                //未生成拣货单 删除erp订单
                if(erpOrders.get(0).getStatus()<=0){
                    jdbcTemplate.update("delete from erp_order where id=?",erpOrders.get(0).getId());
                    jdbcTemplate.update("delete from erp_order_item where orderId=?",erpOrders.get(0).getId());
                    jdbcTemplate.update("update dc_pdd_orders set printStatus=3 where id=?",orderId);
                    return new ResultVo<>(EnumResultVo.SUCCESS,"成功");
                }
                //生成拣货单未出库 删除拣货单与erp订单
                if(erpOrders.get(0).getStatus().intValue()==1){
                    var erpOrderItems = jdbcTemplate.query("select * from erp_order_item where orderId=?",new BeanPropertyRowMapper<>(ErpOrderItemEntity.class),erpOrders.get(0).getId());
                    erpOrderItems.forEach(item->jdbcTemplate.update("delete from erp_stock_out_form_item where itemId=?",item.getId()));

                    jdbcTemplate.update("delete from erp_order where id=?",erpOrders.get(0).getId());
                    jdbcTemplate.update("delete from erp_order_item where orderId=?",erpOrders.get(0).getId());
                    jdbcTemplate.update("update dc_pdd_orders set printStatus=3 where id=?",orderId);
                    return new ResultVo<>(EnumResultVo.SUCCESS,"成功");
                }
                //已出库生成退货单入库
                Integer orderRurunCount= jdbcTemplate.queryForObject("select count(0) from erp_order_return where source_order_num=?",Integer.class,order.getOrderSn());
                if(orderRurunCount.intValue()>0){
                    jdbcTemplate.update("update dc_pdd_orders set printStatus=3 where id=?",orderId);
                    return new ResultVo<>(EnumResultVo.SUCCESS,"成功");
                }
                //已出库，新增原单退货入库
                String returnOrder = "INSERT INTO " + Tables.ErpOrderReturn +
                        " (order_num,orderTime,createTime,contactPerson,mobile,address,source,status,logisticsCompany,logisticsCompanyCode,logisticsCode,source_order_num,shopId)" +
                        " VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?)";
                KeyHolder keyHolder = new GeneratedKeyHolder();

                OrderPddEntity finalOrder = order;
                jdbcTemplate.update(new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(returnOrder, Statement.RETURN_GENERATED_KEYS);
                        ps.setString(1, String.valueOf(finalOrder.getId()));
                        ps.setObject(2, DateUtil.dateTimeToStamp(finalOrder.getCreated_time()));
                        ps.setLong(3, System.currentTimeMillis() / 1000);
                        ps.setString(4, "");
                        ps.setString(5, "");
                        ps.setString(6, "");
                        ps.setString(7, ErpOrderSourceEnum.PDD.getIndex());
                        ps.setInt(8, EnumOrderReturnStatus.WaitReceive.getIndex());
                        ps.setString(9, finalOrder.getTracking_company());
                        ps.setString(10, "");
                        ps.setString(11, finalOrder.getTracking_number());
                        ps.setString(12, finalOrder.getOrderSn());
                        ps.setInt(13, finalOrder.getShopId());
                        return ps;
                    }
                }, keyHolder);
                Long returnOrderId = keyHolder.getKey().longValue();

                //插入仓库退货订单明细 erp_order_return_item
                String returnOrderItemSQL = "INSERT INTO " + Tables.ErpOrderReturnItem + " (orderId,goodsId,skuId,skuNumber,quantity,receiveType,createTime,status,inQuantity,badQuantity,refundAmount,stockInfoItemId) VALUE (?,?,?,?,?,?,?,?,?,?,?,?)";

                var items = jdbcTemplate.query("select * from dc_pdd_orders_item where order_id=?",new BeanPropertyRowMapper<>(OrderPddItemEntity.class),finalOrder.getId());
                for (var item:items){
                    Long stockInfoItemId =jdbcTemplate.queryForObject("SELECT IFNULL((SELECT stockInfoItemId  FROM erp_order_item i LEFT JOIN erp_order e ON i.orderId=e.id WHERE e.order_num=? AND  i.skuId=? LIMIT 1),0) id",Long.class,order.getOrderSn(),item.getErpGoodsSpecId());
                    jdbcTemplate.update(returnOrderItemSQL, returnOrderId, item.getErpGoodsId(),item.getErpGoodsSpecId(),item.getGoodsSpecNum(), item.getQuantity(), 0, System.currentTimeMillis() / 1000, 0, 0, 0,item.getGoodsPrice(),stockInfoItemId);
                }
                jdbcTemplate.update("update dc_pdd_orders set printStatus=3 where id=?",orderId);
                return new ResultVo<>(EnumResultVo.SUCCESS,"成功("+finalOrder.getOrderSn()+")仓库需要手动确认收货");
            }
            return new ResultVo<>(EnumResultVo.SUCCESS,"成功");
        }catch (Exception ex){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail,"系统异常");
        }

    }
    /**
     * 订单发货并确认到仓库
     * @param order
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> orderSendAndConfirm(OrderPddEntity order){
        try {
            //3.确认到仓库
            /**********1.1、开始插入仓库系统订单表erp_order**********/
            StringBuilder orderInsertSQL = new StringBuilder();
            orderInsertSQL.append("INSERT INTO ").append(Tables.ErpOrder);
            orderInsertSQL.append(" SET ");
            orderInsertSQL.append(" order_num=?,");
            orderInsertSQL.append(" totalAmount=?,");
            orderInsertSQL.append(" shippingFee=?,");
            orderInsertSQL.append(" orderTime=?,");
            orderInsertSQL.append(" createTime=?,");
            orderInsertSQL.append(" modifyTime=?,");
            orderInsertSQL.append(" confirmedTime=?,");
            orderInsertSQL.append(" remark=?,");
            orderInsertSQL.append(" buyerFeedback=?,");
            orderInsertSQL.append(" sellerMemo=?,");
            orderInsertSQL.append(" contactPerson=?,");
            orderInsertSQL.append(" mobile=?,");
            orderInsertSQL.append(" province=?,");
            orderInsertSQL.append(" city=?,");
            orderInsertSQL.append(" area=?,");
            orderInsertSQL.append(" areaCode=?,");
            orderInsertSQL.append(" town=?,");
            orderInsertSQL.append(" townCode=?,");
            orderInsertSQL.append(" address=?,");
            orderInsertSQL.append(" source=?,");
            orderInsertSQL.append(" status=?,");
            orderInsertSQL.append(" shopId=?,");
            orderInsertSQL.append(" logisticsCompany=?, ");
            orderInsertSQL.append(" logisticsCode=?, ");
            orderInsertSQL.append(" sale_type=? ");

            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(orderInsertSQL.toString(), Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, order.getOrderSn());
                    ps.setBigDecimal(2, BigDecimal.valueOf(order.getGoods_amount()));
                    ps.setBigDecimal(3, new BigDecimal(0));
                    ps.setLong(4, DateUtil.dateTimeToStamp(order.getCreated_time()));
                    ps.setLong(5, System.currentTimeMillis() / 1000);
                    ps.setLong(6, System.currentTimeMillis() / 1000);
                    ps.setLong(7, System.currentTimeMillis() / 1000);
                    ps.setString(8, order.getRemark());
                    ps.setString(9, "");
                    ps.setString(10, "");
                    ps.setString(11, "");
                    ps.setString(12, "");
                    ps.setString(13, order.getProvince());
                    ps.setString(14, order.getCity());
                    ps.setString(15, order.getTown());
                    ps.setString(16, "");
                    ps.setString(17, "");
                    ps.setString(18, "");
                    ps.setString(19, "");
                    ps.setString(20, ErpOrderSourceEnum.PDD.getIndex());
                    ps.setInt(21, EnumErpOrderSendStatus.WaitOut.getIndex());
                    ps.setInt(22, order.getShopId());
                    ps.setString(23, order.getTracking_company());
                    ps.setString(24, order.getTracking_number());
                    ps.setInt(25,1);
                    return ps;
                }
            }, keyHolder);

            Long erpOrderId = keyHolder.getKey().longValue();

            /**********1.2、开始插入仓库系统订单明细表erp_order_item**********/
            StringBuilder orderItemInsertSQL = new StringBuilder();
            orderItemInsertSQL.append("INSERT INTO ").append(Tables.ErpOrderItem);
            orderItemInsertSQL.append(" SET ");
            orderItemInsertSQL.append(" orderId=?,");
            orderItemInsertSQL.append(" aliSubItemID=?,");
            orderItemInsertSQL.append(" productMallName=?,");
            orderItemInsertSQL.append(" productId=?,");
            orderItemInsertSQL.append(" productNumber=?,");
            orderItemInsertSQL.append(" skuNumber=?,");
            orderItemInsertSQL.append(" skuId=?,");
            orderItemInsertSQL.append(" productImgUrl=?,");
            orderItemInsertSQL.append(" quantity=?,");
            orderItemInsertSQL.append(" status=?,");
            orderItemInsertSQL.append(" itemAmount=?");


            for (var item : order.getItems()) {
                jdbcTemplate.update(orderItemInsertSQL.toString(),
                        erpOrderId,
                        item.getId(),
                        item.getGoodsName(),
                        item.getErpGoodsId(),//这个字段的值已经变成了仓库系统的goodsId
                        item.getGoodsNum(),
                        item.getGoodsSpecNum(),
                        item.getErpGoodsSpecId(),
                        item.getGoodsImg(),
                        item.getQuantity().longValue(),
                        EnumErpOrderSendStatus.WaitOut.getIndex(),
                        item.getGoodsPrice() * item.getQuantity()
                );
            }
            jdbcTemplate.update("update dc_pdd_orders set order_status=2 where order_sn=?",order.getOrderSn());
            return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, order.getOrderSn()+"发货确认到仓库系统异常，"+e.getMessage());
        }
    }

    /**
     * 订单发货
     * @param order
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> orderSend(Long orderId){
        try {
            jdbcTemplate.update("update dc_pdd_orders set order_status=2,shipping_time=? where id=?",DateUtil.getCurrentDateTime(),orderId);
            return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, orderId+"发货确认到仓库系统异常，"+e.getMessage());
        }
    }

    public ResultVo<Long> updOrderRefundSpec(Long refundId, Integer erpGoodSpecId, Integer quantity){
        if(refundId==null || refundId.longValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少orderItemId");
        }

        if(erpGoodSpecId==null || erpGoodSpecId.intValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少erpGoodSpecId");
        }
        if(quantity==null || quantity.intValue()<=0){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，缺少quantity");
        }

        try {

            var orderItem = jdbcTemplate.queryForObject("SELECT * FROM "+Tables.DcPddOrderRefund+" WHERE id=?",new BeanPropertyRowMapper<>(RefundPddEntity.class),refundId);

            if(orderItem.getAuditStatus().intValue()!=0)
                return new ResultVo<>(EnumResultVo.DataError,"订单已经确认，不允许修改");

            /**************************/
            String erpGoodSpecSQL = "SELECT s.*,g.name as goodTitle,g.number as goodsNumber from "+Tables.ErpGoodsSpec+" s LEFT JOIN " + Tables.ErpGoods + " g ON g.id=s.goodsId WHERE s.id=?";
            var erpOrderSpec = jdbcTemplate.queryForObject(erpGoodSpecSQL,new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class),erpGoodSpecId);

            jdbcTemplate.update("UPDATE "+Tables.DcPddOrderRefund+" SET goods_id=?,skuId=?,goods_number=?,sku_number=?,quantity=?,sku_info=? WHERE id=?"
                    ,erpOrderSpec.getGoodsId(),erpOrderSpec.getId(),erpOrderSpec.getGoodsNumber(),erpOrderSpec.getSpecNumber(),quantity,erpOrderSpec.getColorValue()+","+erpOrderSpec.getSizeValue(),refundId);

            return new ResultVo<>(EnumResultVo.SUCCESS,"成功");
        }catch (Exception e){
            return new ResultVo<>(EnumResultVo.ParamsError,"参数错误，不存在orderItem");
        }
    }

    public ResultVo<Integer> updOrderRemark(Long orderId, String remark) {
        String remarkT = jdbcTemplate.queryForObject("select remark from dc_pdd_orders where id=?", String.class,orderId);
        if(StringUtils.isEmpty(remarkT)) remarkT= "";
        remarkT += remark;
        String sql ="update dc_pdd_orders set remark=? WHERE id=?";
        jdbcTemplate.update(sql, remark,orderId);
        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS");
    }

    public ResultVo<Integer> updOrderAddress(Long orderId,String receiverName,String receiverPhone,String receiverAddress){
        String sql ="update dc_pdd_orders set receiver_name1=?,receiver_phone1=?,receiver_address1=? WHERE id=?";
        jdbcTemplate.update(sql, receiverName,receiverPhone,receiverAddress,orderId);
        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS");
    }

    public String getLashOrderCreateTime(Integer shopId) {
        String sql = "SELECT created_time FROM dc_pdd_orders WHERE shopId=? ORDER BY created_time DESC LIMIT 1";
        try{
            String time = jdbcTemplate.queryForObject(sql, String.class,shopId);
            return time;
        }catch(Exception e){
            return null;
        }
    }

    
    public Long getLashRefundCreateTime(Integer shopId) {
        String sql = "SELECT created_time FROM dc_pdd_refund WHERE shopId=? ORDER BY created_time DESC LIMIT 1";
        try{
            Long time = jdbcTemplate.queryForObject(sql, Long.class,shopId);
            return time;
        }catch(Exception e){
            return null;
        }
    }


    public OrderPddEntity getOrder(Long orderId) {
        try{
            String sql = "SELECT * FROM dc_pdd_orders WHERE id=?";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(OrderPddEntity.class),orderId);
        }catch(Exception e){
            return null;
        }
    }

    public OrderPddItemEntity getOrderItemByItemId(Long itemId) {
        try{
            String sql = "SELECT * FROM dc_pdd_orders_item WHERE id=?";
            return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(OrderPddItemEntity.class),itemId);
        }catch(Exception e){
            return null;
        }
    }
}


